Access 2007 query: sorting text numbers

ouestque
ouestque used Ask the Experts™
on
I have a table with a text field. (table is titled: "stuff". Field is titled: "stuffNumber)

"stuffnumber" field contains numbers and text.

What SQL do I need to sort the numbers in the "stuffnumber" field in descending order.
Note: when you try to sort numbers in a text field, numbers don't sort correctly... (I.e. 14.1 will go before 2 if sorting ascending order)

I want to do this in a single query if possible.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
It depends on just what you have in that column.  One way:

SELECT *
FROM stuff
ORDER BY Val(stuffnumber), stuffnumber

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial