Changing Order By

[Middlemarch]

My field can contain both numbers and strings (like A1, B2, C3). I can't easily change this.

I was using Order By on this field, but of course if numbers then 11 comes before 2 etc. Using Val fixes that but mucks up the alphanumeric ones.



Possibly I could check the field first, then use the order by depending on whether Strings or Numbers (There is never both). But is there a better way ?

[ArviLaanemets]

Either split this field to 2 (one field for string part, and second field for numeric part), or make the field to have a fixed length for numeric part, using leading zeros (like "A001", "B002", "C003" or "X999").

[Middlemarch]

Thanks for the reply. I'm a bit restrained in what changes I can make. Ended up with

 rx.FindFirst ("ATrack= '" & "11'")
 If rx.NoMatch = False Then myQuery.sql = Replace(myQuery.sql, "ATrack", "Val([ATrack])")

And doing some testing to see if that's enough.

[NoellaG]

create a calculated, numeric field in the query: if the original field is numeric, then take the numeric value, else 9999999999999999999 or something like that. Then create the sort order on 2 fields: first on calculated field, second on original field.