[AccessD] Sorting Alpha-Numerics

Holy cow...I'm in awe.  I remember a quote from this list a long time ago
that seems appropriate at the moment.  It read something like "Gustav is a
legend...cha cha cha"

Thank you sir,


Mark



-----Original Message-----
From: Gustav Brock [mailto:gustav at cactus.dk] 
Sent: Tuesday, October 21, 2003 11:57 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Sorting Alpha-Numerics


Hi Mark

Oh yeah, one more go to format leading zeroes:

.. Abs(StrComp(strVal, Right("00" & CStr(Val(strVal)), 3), 1))

/gustav


>> Order By Abs(StrComp(strVal, Cstr(Val(strVal)), 1)) ASC, strVal ASC

> Hmm...on this iteration 100-999 sorted properly first, the remaining 
> records reverted back to the way it was before...

> 001
> .
> 009
> 00A
> .
> 00Z
> 010
> .
> Etc...



> Mark


> -----Original Message-----
> From: Gustav Brock [mailto:gustav at cactus.dk]
> Sent: Tuesday, October 21, 2003 11:11 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Sorting Alpha-Numerics


> Hi Mark

>> Oooh...that is a little closer Gustav.  You must be on the right 
>> track. That approach put 001 through 019 in order before jumping to 
>> 01A.

> Of course ... StrComp returns -1 for that 01A.

> This should do:

>   Order By Abs(StrComp(strVal, Cstr(Val(strVal)), 1)) ASC, strVal ASC

> /gustav


>> You could do this SQL

>>  .. Order By StrComp(strVal, Cstr(Val(strVal)), 1) ASC, strVal ASC

>> /gustav


>>> 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

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