If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sorting by Text field of numbers
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 |
#2
|
|||
|
|||
Sorting by Text field of numbers
On 20.01.2010 18:14, Aurora wrote:
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 -- |
#3
|
|||
|
|||
Sorting by Text field of numbers
Aurora wrote:
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. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|