[AccessD] Re: Sorting Alpha-Numerics - SOLVED:

Three approaches were presented that worked according to my criteria.  All 3
were 'one query' solutions.  No speed comparisons were run between them.

---------------------------------
PROBLEM
---------------------------------
I have a text field containing all possible 3 digit alpha-numeric
combinations from 000 to ZZZ.  I am looking for a method to sort this text
field where 000 through 999 are listed in numerical order before the
remaining alpha-numeric combinations are listed in ASCII sort order.


---------------------------------
SOLUTIONS
---------------------------------
Gustav Brock:
SELECT tblCodes.fldCode
FROM tblCodes
ORDER BY abs(StrComp(tblCodes.fldCode, Right("00" &
Cstr(Val(tblCodes.fldCode)), 3), 1)), tblCodes.fldCode;


A.D.Tejpal:
SELECT IIf(IsNumeric([fldCode]) And IsNumeric(Mid([fldCode],2,1)),"A","B")
AS F1, tblCodes.fldCode
FROM tblCodes
ORDER BY IIf(IsNumeric([fldCode]) And
IsNumeric(Mid([fldCode],2,1)),"A","B"), tblCodes.fldCode
WITH OWNERACCESS OPTION;


Lambert Heenan:
SELECT tblCodes.fldCode
FROM tblCodes
ORDER BY IsNumeric([fldCode]) And Mid([fldCode],2,1) Not In ("D","E"),
tblCodes.fldCode;
---------------------------------