[AccessD] Sorting textual fields like number fields?

Depends if you want all houses in same street together. The Val on its own
won't give you that, you'll need to strip out the number and the street
somehow and then sort on the Street followed by the Val.

Andy Lacey
http://www.minstersystems.co.uk 

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Liz Doering
> Sent: 02 December 2003 18:54
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [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/a> ccessd
> Website: 
> http://www.databaseadvisors.com
> 
> 
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/a> ccessd
> Website: 
> http://www.databaseadvisors.com
> 
>