Results 1 to 6 of 6
  1. #1
    BetteB
    Guest

    Sorting Text and Numbers

    I'm tracking clothing inventory for a volunteer group. The clothing sizes are text fields with numbers (6, 8, 10) as well as text(6X, S, M). When I sort, 10 is sorted before 6. How can I sort this field with numbers and letters sorted in "normal" (human?!) order.

    Thanks

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Text and Numbers

    If you have letters as well as numbers in the field than it has to be a text field and that's the way text fields sort. What I would do is add anotherfield to the table to sort by. This would be a numeric field.

    Do a make table query on the table and get a unique list of all the sizes. Then in that table assign each size a number so that when you sort by that number the sizes are in the order that you prefer. Then do an update query on you table to add those numbers to your sort field.

    Sorry, I don't know of any other way.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Text and Numbers

    It sorts "6" after "10" for the same reason you would sort "I" after "AN" alphabetically - it is natural if you think of the digits simply as extra "letters" when you sort a text field.

    My immediate thought was to think up a way to right-justify your sizes in a two (or more) character field. However, that would still cause problems with 6 vs 6X, and where would you want S, M, L, XL, XXL etc. to go?

    To do a sort your way (regardless of what your way is), create a two field table that has a (text) size in one field and its (numeric) sort position in the other field, e.g. if you want "S" to come first in the sort order you could put "S" in the size field and 1 in the sort position field. You can then set up a query that relates your main table with that new table and sort by the sort position.

  4. #4
    BetteB
    Guest

    Re: Sorting Text and Numbers

    Thanks so much for the great suggestion - I will try it!

  5. #5
    New Lounger
    Join Date
    May 2002
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Text and Numbers

    You can sort on val(text_size), text_size and get what you want without creating new fields.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Text and Numbers

    Sorting on val(text_size),text_size will work provided S, M, L, XL, etc, aren't allowed for. It would probably be wise to allow for all possibilities since having a separate sort order table is easy enough to implement.

Posting Permissions

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