[AccessD] Re: Sorting Alpha-Numerics

Guys,

Just a thought, why not limit it using 2 different queries.
The first one would hold the numerics sorted correctly.
The second one would hold the alphanumerics sorted correctly.
Then, do a union between them.  There is nothing you can
do that will cause it to sort the way you are looking at
within one query.

SELECT *
FROM TT
WHERE Asc(Mid(TheField)) < 64
ORDER BY TheField

And

SELECT *
FROM TT
WHERE Asc(Mid(TheField)) > 64
ORDER BY TheField

Then:

SELECT * from Query1
UNION ALL
SELECT * FROM Query2

This should work in theory.

Robert

At 11:24 AM 10/21/2003 -0500, you wrote:
>Message: 11
>Date: Tue, 21 Oct 2003 10:26:32 -0500
>From: Drew Wutka <DWUTKA at marlow.com>
>Subject: RE: [AccessD] Sorting Alpha-Numerics
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Message-ID:
>         <2F8793082E00D4119A1700B0D0216BF8022273AA at main2.marlow.com>
>Content-Type: text/plain;       charset="iso-8859-1"
>
>Do a double sort.  Create another field, which is the VAL of your data.
>Sort that descending, and if the val=0 then set it to 1000, that put's it
>after the other numeric numbers, then sort alphabetically.
>
>Drew