Sort order Text, Numerical "Both ?"

[IanS]

In my SELECT query I'm using ORDER BY to sort the data in my recordset.

The field we're sorting on is a text field but it often contains numerical data.

As I'm sure you know this results in numbers being sorted like 1, 10, 100, 2 ,3 etc (In text sort "100" comes before "2". We all understand that so no need to explain in more detail)

But what I'd like to be able to do is - Have all the rows containing ONLY numbers sorted numerically 1, 2, 3, 10, 100 etc and then, either at the start or end of the recordset, have all the rows containing any text sorted as text.

I suppose the best way would have been to design this in from the start and have the data stored in two separate columns in the database - A number column and a text column. But it's an old database, lots of users and I'm really looking for some way to improve the sorting on the existing data.

Any ideas ?

Thanks
Ian

Edit - I forgot to say - This is currently in an access 2000 database - all code is in a vb6 application - there is no code, forms, queries, macros etc in the database. The database is just somewhere to store data. All logic must be in the application.