Sorting Numbers in Text Field
Author Message
Sorting Numbers in Text Field

Hi, is there a SQL command that will sort numbers in a text field that also
contain alpha numeric numbers.. I have numbers like 5, 5A, 5B, 5C, 6..  In
my SQL statement, I tried :  "... ORDER BY Val([Column])", but it doesn't
sort it properly.. Can someone help?
Thanks
John

Sat, 19 Apr 2003 03:29:50 GMT
Sorting Numbers in Text Field
I'd bet it is sorting quite properly -- for text. And, no there's
not "a SQL command" that will do what you want, but it is possible to
do it.

"Alpha numeric numbers"? That's an oxymoron. "Codes that are a
combination of numbers and letters" would be a better description,
because, in fact, "5A" _isn't_ a _number_.

You want them to sort as though they were numbers? But, I'll bet you
still want the alphabetic part to enter into the sorting, too, don't
you?

Now, this will not necessarily work for just any SQL, but it will work
for Access' SQL, because you can use Access' builtin functions (at
least most of them) in Access Queries/SQL.

Here are two functions:

Function NumPart(pstrAN As String) As Long
NumPart = Val(pstrAN)
End Function

Function AlphaPart(pstrAN As String) As String
AlphaPart = Right\$(pstrAN, Len(pstrAN) + 1 - Len(Str(Val(pstrAN))))
End Function

Here's the SQL for a Query:

SELECT NumPart([AN]) AS NVal, AlphaPart([AN]) AS AVal, tblAN.AN,
tblAN.KeyField
FROM tblAN
ORDER BY NumPart([AN]), AlphaPart([AN]);

Here's a table, tblAN:

KeyField     AutoNumber
AN           Text         (left at default length of 50)

That orders the entries in tblAN, first by the numeric part, then by
the alpha part, of the code. CAVEAT: This only works for codes in the
form you showed -- numeric part first, followed by the alphabetic part.
It does seem to work as well for numbers with no alphabetic part
following, too.

Here are the codes ("numbers") that I used in field "AN" for testing:

100
345A
99B
8CD
763X
763A
99A
6

And here's the order in which they were displayed by the query:

6
8CD
99A
99B
100
345A
763A
763X

Quote:
> Hi, is there a SQL command that will sort numbers in a text field
that also
> contain alpha numeric numbers.. I have numbers like 5, 5A, 5B, 5C,
6..  In
> my SQL statement, I tried :  "... ORDER BY Val([Column])", but it
doesn't
> sort it properly.. Can someone help?
> Thanks
> John

--
L. M. (Larry) Linson
http://www.ntpcug.org - North Texas PC User Group - Visit and Join