Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    8

    Unanswered: Sort text field as a number..

    Hi,

    I am new to Access and VBA and have a problem.
    I have a field with a text data type but it contains primarily numeric data. I want to sort the numeric data as I would if it were a number field (i.e.: 1,2,3,4,5,etc.) but it sorts it as text from left to right (1,10,2,20, 21, 3, 30, etc.). Is there a way to get the numeric date to sort as a number and still have the text sort as text?

    Thanks in advance for your help!

  2. #2
    Join Date
    Nov 2004
    Posts
    8
    I have thought of another possibility...
    I can switch the data type of the field to number and I can use zero (0) to represent the one text item i want to store here. However, I need to be able to display the item as text on my forms and reports. Is there a way to have alternate text display in the form or report if the value in the field is equal to zero?
    I need the letters HOA to display, instead of the zero.

  3. #3
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Here is a better solution that doesn't require that you put a 0 in where you wanted the alpha text. Create a query and include all of the fields that you need for the report. Create another field and define it as '=Format([yourfieldname],"000000")' and then sort based upon this field. Uncheck the 'Show:' box. I didn't know what your largest number would be so I used "000000", which would accomodate a six digit number.

    TD

  4. #4
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    or add leading zero´s in front of the number (they are interpreted as txt])

  5. #5
    Join Date
    Nov 2004
    Posts
    8
    If I use the leading zeros, how would I enforce entry of the zeros. It will always be a 3 digit number such as 001, 101, 405, etc. or the text phrase HOA. Can I use a table level validation rule requiring at least entry of 3 characters? Not sure how to build that expression. I could set the default value to 000 to remind me to enter 3 characters... Thoughts?
    Thanks

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by barnettcb
    Hi,

    I am new to Access and VBA and have a problem.
    I have a field with a text data type but it contains primarily numeric data. I want to sort the numeric data as I would if it were a number field (i.e.: 1,2,3,4,5,etc.) but it sorts it as text from left to right (1,10,2,20, 21, 3, 30, etc.). Is there a way to get the numeric date to sort as a number and still have the text sort as text?

    Thanks in advance for your help!
    Hi barnettcb and "Welcome to the Forum"

    Now to the matter at hand, if you change your field to Number do this. In the TableDesign view, when you change it to Number, look down below in the FieldProperties section for that field, set the FieldSize to LongInteger, then on the line that says Format, type this......(0000).
    That being use the Open Parenthesis first, next, type in the amount of Zero's that fit your FieldSize, then use the Close Parenthesis. That's all there is to it. IF it doesn't carry over into your form, simply do to the FormDesign view, select that field and then to FieldProperties, and as before, on the FormatTab place the same thing again. (0000)

    that's about it and hope you have a nice one,
    BUD

  7. #7
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    OH, just for the sake of it....some people call it ZeroPadding.

    cya
    Bud

  8. #8
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    barnettcb said he needed to have alpha values in the field also, so changing the field to a number doesn't work. If you create a query of this file as it exists and set up a field with formatting for the zero padding as I suggested on 11/05, you can sort on that field and you don't even need to make it visible on the report or the form.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •