[AccessD] Sorting Alpha-Numerics

    You are welcome Mark !

Regards,
A.D.Tejpal
--------------
  ----- Original Message ----- 
  From: Mitsules, Mark 
  To: 'Access Developers discussion and problem solving' 
  Sent: Wednesday, October 22, 2003 00:58
  Subject: RE: [AccessD] Sorting Alpha-Numerics


  A.D.Tejpal,

  Yes, this approach works.  Well done:)


  Thank you,

  Mark
  (Although it is not an issue, it is too bad I don't have a timer utility
  built to test the solutions that work.)

  -----Original Message-----
  From: A.D.Tejpal [mailto:adtp at touchtelindia.net] 
  Sent: Tuesday, October 21, 2003 2:38 PM
  To: Access Developers discussion and problem solving
  Subject: Re: [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