[AccessD] Sorting Alpha-Numerics

Tue Oct 21 14:22:38 CDT 2003

Mark, how about:

SELECT Table1.Field1
FROM Table1
ORDER BY IIf(IsNumeric([Field1]),0,1), Table1.Field1;

IT gives me:

Field1
000
001
002
003
004
005
006
007
008
009
998
999
00A
00B
ZZZ

-----Original Message-----

  From: Mitsules, Mark 
  To: '[AccessD]' 
  Sent: Tuesday, October 21, 2003 19:28
  Subject: [AccessD] Sorting Alpha-Numerics

  Greetings,

  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.  Any
  suggestions?  I have tried to do this in Excel, however, Excel makes some
  startling decisions on its own...such as turning 7E9 into 7000000000.

  Example:
  000
  001
  .
  .
  998
  999
  00A
  00B
  .
  .
  ZZZ

  Mark Mitsules