sorting addresses

#1 (**permalink**) 28-Jul-2006, 08:29 AM

I am trying to sort a field that is 345 south main street, when I sort the
column of address they appear lowest numeric number first.
How do I convert 345 south main street and so on that is in one field to 2
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12445-sorting-addresses.html
fields: 345; south main street, so that the sort works properly?

*

#2 (**permalink**) 28-Jul-2006, 08:29 AM

Use two calculated fields in the query as the sort fields. Something like
this:

SELECT * FROM Tablename
ORDER BY Trim(Left([AddressField], InStr([AddressField, " ")-1)),
Trim(Mid([AddressField], InStr([AddressField, " ")+1));

--

Ken Snell

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12445

"sternface" wrote in message
news:C252BA22-B1F6-4003-BCEC-642F8751407C@microsoft.com...
>I am trying to sort a field that is 345 south main street, when I sort the
> column of address they appear lowest numeric number first.
> How do I convert 345 south main street and so on that is in one field to 2
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12445
> fields: 345; south main street, so that the sort works properly?

#3 (**permalink**) 28-Jul-2006, 08:29 AM

To add to my post, you might have to cast the first part of the field as a
number to get numeric order sorting:

SELECT * FROM Tablename
ORDER BY CLng(Trim(Left([AddressField], InStr([AddressField, " ")-1))),
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12445
Trim(Mid([AddressField], InStr([AddressField, " ")+1));

--

Ken Snell

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12445

"sternface" wrote in message
news:C252BA22-B1F6-4003-BCEC-642F8751407C@microsoft.com...
>I am trying to sort a field that is 345 south main street, when I sort the
> column of address they appear lowest numeric number first.
> How do I convert 345 south main street and so on that is in one field to 2
> fields: 345; south main street, so that the sort works properly?