How do I sort address by street name, not by number?

J

Jeff Boyce

It all starts with the data.

How is your data stored?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
F

fredg

How can I sort address by street name, not by house number?
Click to expand...

Street numbers come in many forms:
123 Main St.
123 1/2 Main St.
123 A So. Main St.
etc.

If ALL of the addresses are in the form of 123 Main St. than all you
need do, in a query is add a new column...
SortStreet:Mid([FullAddress],InStr([FullAddress]," ")+1)

Use this new field to sort your query on.
If you are wanting to use this sort in a report, you must use the
Report's Sorting and Grouping dialog
View + Sorting and Grouping
as the sort order of a query or table is irrelevant in a report.

This will not work if the Address form is one of the other forms.
 
F

Fred

A common situation for voter databases. Election commissions usually handle
it by splitting up the "street address" line into three fields:

- Address Number (everthing before the street name)
- Street Name
- Apartment / Unit number
Plus make up a few rules for the details / exceptions

So, "2346 Main St. , Apartment #101" becomes

- Address Number (everyhing before the street name) 2346
- Street Name Main
- Apartment / Unit number Apt. 101





fredg said:
How can I sort address by street name, not by house number?
Click to expand...

Street numbers come in many forms:
123 Main St.
123 1/2 Main St.
123 A So. Main St.
etc.

If ALL of the addresses are in the form of 123 Main St. than all you
need do, in a query is add a new column...
SortStreet:Mid([FullAddress],InStr([FullAddress]," ")+1)

Use this new field to sort your query on.
If you are wanting to use this sort in a report, you must use the
Report's Sorting and Grouping dialog
View + Sorting and Grouping
as the sort order of a query or table is irrelevant in a report.

This will not work if the Address form is one of the other forms.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sorting 7
Sort a table by street name using Access 2007 2
How do I sort a list of addresses by street name (ignoring the ad. 2
How do I sort postal addresses by street name, then number 5
Sorting Addresses 2
Delete numbers in text field 3
separating street numbers and street names 4
Word Create a Phone Book using MailMerge in word 2007 0

Top