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

  • Thread starter Thread starter ccsu17
  • Start date Start date
It all starts with the data.

How is your data stored?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
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.
 
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...
 
Back
Top