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
  http://www.ntmsdevsigs.homestead.com - NTPCUG Developer SIGs
  http://homestead.deja.com/user.accdevel - Access examples

Sent via Deja.com http://www.deja.com/
Before you buy.



Sun, 20 Apr 2003 05:03:34 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Sorting Numbers in Text Field

2. Convert number field to text field.

3. Concatenating a Text Field and a Number Field

4. Sort field by number

5. URGENT: Sorting Text Fields

6. Sorting Text Fields

7. Having problems with sorting character or text fields

8. Exporting a 3 decimal number to text file produces trunc 2 decimal text number

9. Sort on Text Field

10. Sorting Text fields in Numeric Order

11. SQL sort on text field

12. SQL sort on text field

 

 
Powered by phpBB® Forum Software