Sorting by numbers 1

[Duinne]

I have a database that I am using to do registrations for a car race. The entry list I have created shows the car number, driver name, hometown, etc. I want the list of entries to sort by car number, but right now when I sort it I get the following type of list:

02
12
23
4
66
7
83
99

Is there a way I can get the single digits to all sort first, before the numbers that start with a zero, and then the double digit numbers list after that. I want it to sort like a regular list of numbers would, 0-9 then 00-09 then 1-99. I can't just put a zero in front of the 4 and the 7 because drivers can choose 04 and 07 as car numbers, as well as 4 and 7, so I could end up with both 04 and 4 in the same list.

Right now the field is a text field because of things others have to do with the lists. But somebody told me it wouldn't matter, even if I made it a number field it would still sort the same way.

Thanks for any help!

 

[jjonesal]

Create a calculated field in your query (just to use for sorting) that converts the text field to a numeric field.

Something like:

ncarnum: CInt(carnum)


Then, include this field & the original carnum field in your query result - then you can sort by ncarnum & use carnum (the text field) when you need to.


Hope this helps...

jj
jjones@cybrtyme.com

 

[Duinne]

Thanks, I'll try this tonight and let you know if it works