MS Access database number sorting trick?

Hi,
Does anyone know of a MS Access database number sorting “trick” whereby when sorting a field of numbers it will sort then in order even without having to add zeros in front?
Example:
5
6
7
8
9
10
11
If I sort this field, it will put 10 and 11 first, so I have to put zeros in front like:
05
06
07
08
09
10
11
in order to get it to sort properly.
I’ve done that up to now, but once getting into the hundreds, it gets messier. I would have to have TWO zeros in front in that case and don’t want to do that.

Any tips?
Thanks,
RB

Store them as numbers (not done), not as strings of the number (which is being currently done). Then they should sort as you want. Set up a separate field that is the (numeric value (x)=string value (x)), where x is the number you want. Did it in BASIC lots of times. Should be similar in Access. Modified Access database to expand 4-digit RMA values (1-9999) to 5-digit RMA (1-99999). Just took a few minutes.

5 Likes

Thanks Jerry, simply changing that field from “text” to “number” worked immediately (I backed up the file first of course).
One problem I’ll have to consider: a bunch of my records had other than numbers in that field and those get deleted.
Example: database of book numbers, where some of the “numbers” aren’t just numbers, but might say something like “Vol. 1”. I’ll have to consider changing these records before I make the change in the original file.

But the idea of simply changing the field format from text to number is inspired! So easy. Kind of obvious, but apparently not for me!
Thanks.

That was why I recommended a separate field. Things with non-numeric values get set to or zero in such a conversion. Then it is easy to sort on the new field–and you can create your own numbering system so you can find what you want.

Thanks for your help :slight_smile:

1 Like

If a number isn’t really a number, but part of a text field, one trick I use to get titles to sort correctly is to insert an extra space in front of the number. I do this with titles of discs I have rented or watched, particularly for shows that run 10 discs or more, or (rarely) runs 10 seasons or more.

Where a number or a date is really a number or date (or missing/null is ok), I usually try to use a data type that matches what the field is expected to contain, and then that field sorts just fine.