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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.