[AccessD] Sorting textual fields like number fields?

Christopher,

SELECT Val([Address]) AS SortOrder, [Address].[Address]
FROM Address
ORDER BY Val([Address]);

If it's pneumonia, you'll feel better after some extensive sleep.

Liz 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Christopher
Hawkins
Sent: Tuesday, December 02, 2003 12:28 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Sorting textual fields like number fields?

All,

I feel like I should already know how to do this, but for whatever
reason - I don't.  Must be the pneumonia clouding my thinking. 
Here's the problem:

I need to sort an address field by street number.  The Address
(street number and street name) is contained in a Text field, so my
attempts to sort it end up with the numbers being sorted like text.
For example, given addresses 1118 Main St., 1144 Maple St., 1146 Oak
St., 113 East St. and 115 West St. the sort would look like this:

1118 Main St.
113 East St.
1144 Maple St.
1146 Oak St.
115 West St.

I need the sort to go exactly by street number, like this:

113 East St.
115 West St.
1118 Main St.
1144 Maple St.
1146 Oak St.

And I don't have a clue how to begin.  Heeeeeeeeeeeeeeeeeeeeeelp...

-Christopher-


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com