sorting addresses

[Guest]

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?

 

[Ken Snell \(MVP\)]

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 \(MVP\)]

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))),
Trim(Mid([AddressField], InStr([AddressField, " ")+1));