[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

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com