Sorting Alpha and Numeric data in a Select query field 1

[Locoman]

Hi folks,

I am having great difficulty in try to create a Select query which will extract both alpha and numeric data from a Text field in a Parts database correctly.
I have two tables called "old" and "new" and I need to compare one specific field called "revision".
The data significance for "revision" needs to work in the ascending sort order A,B, n then 1,2,3 etc. i.e. 1 is later than A.

I want to select all records using

revision newtable > revision oldtable

but in the sorting hierachy it appears that alpha characters are always greater than numeric. This result is that any old revision numbers, which were previously alpha and have now changed to numeric, are not selected by the query.

Is there a better way to perform the inspection test?

thanks in advance

 

[jrbarnett]

Put an extra calculated column in your field set to

Iif(Asc (Revision) >= 49 And Asc (Revision) <= 58,Asc(Revision)+123,Asc(Revision))

This will return the ASCII values of each character and add 122 to the value of any which is a number (0 to 9 are 49 - 58), and adds 123 to it - the value of lowercase z plus 1.
You can then sort on this column.

John

 

[Locoman]

Thanks John, that's brilliant!
It took a bit of juggling to get the syntax of the actual &quot;revision&quot; field name reference correct from both tables, but the mapping works fine. Wish I'd thought of it myself!