Sorting strings that contain text and numbers (1 Viewer)

mitchem1

Registered User.
Local time
Today, 16:11
Joined
Feb 21, 2002
Messages
153
I've seen other posters run into this problem, but I still haven't been successful with any of the replies. My column (text data type) contains strings such as 1016+45, 695+00, 818+12, etc. It sorts in the order listed; I need it 695+00, 818+12, 1016+45. I have tried the Val function which works until I try to sort. Then I get a data type mismatch error. I thought maybe the Format function -- something like Format([StationOne], "0000000000") would work, but it does not place leading zeros before the string. It returns the same thing as [StationOne]. Any ideas would be greatly appreciated.
 

EndersG

Registered User.
Local time
Today, 21:11
Joined
Feb 18, 2000
Messages
84
SELECT IIf(Len([NameOfField])=6,[NameOfField],"0" & [NameOfField]) AS FormattedField
FROM NameOfTable
ORDER BY IIf(Len([NameOfField])=6,[NameOfField],"0" & [NameOfField]);

The idea here is that for all the fields with 6 characters (i.e, 695+00 and 818+12), a leading zero is placed in front of the text. All others (i.e, 1016+45) are left unformatted. Then all that's left to do is to order the field by the above formula.

Note: This only applies to records where the number of characters are either 6 or 7. To extend this query to apply to values of either less or more than 6 or 7 characters, you have to adjust the query accordingly. For example, extend the Iif clause to include conditions for 5 characters (add "00") or 4 characters (add "000"). You get my drift. Hope this helps.

Edward
 

mitchem1

Registered User.
Local time
Today, 16:11
Joined
Feb 21, 2002
Messages
153
That worked great. Thanks a lot!
 

Users who are viewing this thread

Top Bottom