Sort order Text, Numerical "Both ?"

[si_the_geek]

This is currently in an access 2000 database

In that case try this:

SELECT <fields>
FROM <table>
WHERE IsNumeric(<fieldtosortby>) = True
ORDER BY CLng(<fieldtosortby>)

UNION ALL

SELECT <fields>
FROM <table>
WHERE IsNumeric(<fieldtosortby>) = False
ORDER BY <fieldtosortby>

Sorry, I also forgot to say (in case it's relevant) All reading and writing to the database is done using SQL - I never use data objects, bound grids or anything like that.

Good

I also use DAO (please don't tell me to use ADO - I don't have any problem using ado. I do lots of work with ado - but the argument about which is better/faster against jet databases is a different topic )

Which is faster is irrelevant, DAO died many years ago (even the VB6 help [written in 1998] says not to use it for any new projects), and it now fails in many situations - such as 64-bit versions of Windows.

ADO is still current.