Sort on Address Field

[Guest]

If an address was stored in one field how do you sort so that the data is
sorted by number and street? I want all addresses on the same street to list
together in numeric order.

Any help would be appreciated.

Thanks,

Sarah

 

[Van T. Dinh]

In this case, the Field value stores multiple items of data (according to
your usage) and the database is NOT normalized.

While there may be work-arounds, the best way in the long run is to
normalize your database and use 2 Fields: StreetNumber and StreetName
instead of 1 Field.

 

[John Vinson]

If an address was stored in one field how do you sort so that the data is
sorted by number and street? I want all addresses on the same street to list
together in numeric order.

Any help would be appreciated.

Thanks,

Sarah

Van's suggestion is your best bet, if you want to treat Streets as a
valid (sortable, searchable) entity. Use two fields.

As a partial getaround you can use two calculated fields:

StreetName: Mid([Address], InStr([Address], " ") + 1)
StreetNo: Val([Address])

This will give anomalous results for addresses like "212 B Baker St",
"1812 1/2 Zenith Ave.", "Bush House" and the like, but it should be a
start.

John W. Vinson[MVP]

 

[Joseph Meehan]

If an address was stored in one field how do you sort so that the
data is sorted by number and street? I want all addresses on the same
street to list together in numeric order.

Any help would be appreciated.

Thanks,

Sarah

Van is right. Do do that kind of sorting you will need to divide the
address information into various fields. I would suggest

Number
Street Name (I would include type as well like "Walnut Rd"
City
State
Zip
You also may want to add fields for international addresses and things like
PO Box (you may have that and have a street address) Room number or floor
number or building name. Take a good look at all the variations you now
have and consider what you may need in the future and what you may want to
sort on.

The problem with trying to pick stuff out of one field is there are too
many variations of address forms that it is almost impossible to pick out
say the street name other than doing it manually.