# Thread: Sorting Text and Numbers

1. ## 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. ## 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. ## 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. ## Re: Sorting Text and Numbers

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

5. ## Re: Sorting Text and Numbers

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

6. ## 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
•