Sort on Address Field

S

Sarah

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
 
V

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.
 
J

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
Click to expand...

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]
 
Top