order by formstalphanumeric field

[gecko_1]

Hi,
In one of my tables I have an alphanumeric field that we now need to sort by.

the usual values in the field are like: 1, 5a, 10, 10a, 20, 50a, 50b, 999

Using order by will sort 1, 10, 10a, 20, 5a,...

Is it posaible to use some sort of format or filter in the sql to only select the numeric part of the field.

eg. format(10a, "????") ???? been some format string would = 10.

Thanks for any help or hits.

Todd

 

[Jon K]

Type/Paste in the SQL View of a new query, replacing with the correct table name and field name:-

SELECT [TableName].*
FROM [TableName]
ORDER BY Val([AlphaNumericField]), [AlphaNumericField];
.

 

[gecko_1]

Thanks alot, working well so far,

save a huge amount of stress.

Cheers