Recordset order not woring with numbers

When I use order by on a acces text field that contains numbers the order does't appear to be working.
Is this because the text are numbers. Heres my code.
.RecordSource = "select * from [Family History] where  [Family #] ='" & FamilyNum & "' order by [Order Number] desc"
SHouldn't this arrange the records in descending order via the order number?
Thanks
whiwexAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
cfitConnect With a Mentor Commented:
For numbers in a text field, you need to either left-fill the field with zeroes to get the order to sort correctly, as for example, 456 will sort after 1000, whereas 0456 will sort before 1000 (the comparison starts from the left-most character - ie "4" vs "1" in that example), or order by Val([Order Number]) instead.

Using Val is not ideal, as it will have to perform that calculation on every row in the table.  It would be better to either left-fill the numbers, or just use a numeric field.
0
 
Arthur_WoodCommented:
are you saying the the field named [Order Number] is defined as a TEXT field in the database, but that it actually contains DIGITS (notice I did not say 'numbers')

the characters "1", "12", "123"  are quite different from the numbers 1,12,123

for example the character sequence "123" will be sorted as LOWER that the character sequence "23"

where , as NUMBERS, 23 is clearly LOWER that 123

AW
0
All Courses

From novice to tech pro — start learning today.