[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