Sorting by Text field of numbers

[Aurora]

I am using Access 2003.
I have created a database to help me files by "Lot#'s". Our lot numbers for
one of our products is all over the place so I created the Lot # field as a
text field. Ex: 02-01-01, 10000272925, 10-06-02-04-06 and 1503011 A1-02.
Why does access put - 1000272925 before 10-06-02-03-05? Does this look right
to anyone?

My thinking is that "10" comes before "1000272925". What affect does the
"-" have in sorting the numbers. Would I be better off to put a space
instead of the "-"? Can anyone help me with this problem?

Aurora

 

[Stefan Hoffmann]

I am using Access 2003.
I have created a database to help me files by "Lot#'s". Our lot numbers for
one of our products is all over the place so I created the Lot # field as a
text field. Ex: 02-01-01, 10000272925, 10-06-02-04-06 and 1503011 A1-02.
Why does access put - 1000272925 before 10-06-02-03-05? Does this look right
to anyone?

Yes, because the hyphen is in the ASCII order after the numbers.

You may try using Replace() in your order criteria:

ORDER BY Replace([Lot#'s], "-", "")

You may need some left padding also, e.g.

ORDER BY
Right("0000000000000000" & Trim(Replace([Lot#'s], "-", "")), 16)


mfG
--> stefan <--

 

[Marshall Barton]

I am using Access 2003.
I have created a database to help me files by "Lot#'s". Our lot numbers for
one of our products is all over the place so I created the Lot # field as a
text field. Ex: 02-01-01, 10000272925, 10-06-02-04-06 and 1503011 A1-02.
Why does access put - 1000272925 before 10-06-02-03-05? Does this look right
to anyone?

My thinking is that "10" comes before "1000272925". What affect does the
"-" have in sorting the numbers. Would I be better off to put a space
instead of the "-"? Can anyone help me with this problem?

Those are NOT numbers, they are text strings that are being
sorted in the usual dictionary order. If you want them
sorted as if they were numbers, you will have to convert
them to actual numbers by removing the non digits and using
a conversion function (Val, CLng, ?) to get actual numbers
for sorting.