Sorting Addresses

[Guest]

When addresses are stored in a text field, how do I sort address so that the
addresses are sorted by street name and then sorted by address?

Example:
123 Blue Star Ct.
1456 Blue Star Ct.
123 Caribou Dr.
1456 Caribou Dr.

The number of house digits will vary.

 

[J]

You have to cleverly create a new field in a query, which would look
something like:

StreetName: Mid([Address],Instr([Address]," ")+1,20)

This assumes that the street name is always after the first space
though. Then sort by the new field.

 

[John W. Vinson]

When addresses are stored in a text field, how do I sort address so that the
addresses are sorted by street name and then sorted by address?

Example:
123 Blue Star Ct.
1456 Blue Star Ct.
123 Caribou Dr.
1456 Caribou Dr.

The number of house digits will vary.

And they, and the street names, may vary more than you think:

35A Cloyne Court
1812 1/2 Barrand <will sort by 1/2 not by Barrand>
251 W. Main <do you want it sorted by W or by Main?>
Ballacraine <that's a friend's legal address: no number no street>

If you want to sort by street name, you should really consider breaking down
the field. An Address isn't really "atomic", it has finer components. I'd
suggest having AddressNo, Direction, Street, and Suffix - this will let you
sort or search by each component independently of the others, and you can
easily combine them for display.

John W. Vinson [MVP]