[AccessD] Sorting Alpha-Numerics

Mark,

    Could you kindly try the following and let me know -

    Let table T_Sort have a field SVAL (text type), containing  the alphanumeric values. In query design window, create two fields: 
    (a) First field named F1(calculated field) with the formula -
F1: IIf(IsNumeric([SVAL]) And IsNumeric(Mid([SVAL], 2,1)), "A","B")
    (b) Second field  SVAL
    Sort order for both fields may be set to ascending.

    SQL string for the above query is given below -

SELECT IIf(IsNumeric([SVAL]) And IsNumeric(Mid([SVAL],2,1)),"A","B") AS F1, T_Sort.SVAL
FROM T_Sort
ORDER BY IIf(IsNumeric([SVAL]) And IsNumeric(Mid([SVAL],2,1)),"A","B"), T_Sort.SVAL
WITH OWNERACCESS OPTION;

Regards,
A.D.Tejpal
--------------
  ----- 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
  Senior Designer
  Department E13
  Submarine Electrical Engineering
  Northrop Grumman Newport News
  757.380.3376