Sorting street addresses

[Guest]

I have a database that tracks individuals by their street address. My field
is setup as ### Street Name; however, the addresses have anywhere from 3
digits to 5 numbers and some have East, West, South, etc. before the actual
name of the street. Is there someway of creating a filter that allows me to
sort by street name and then number?

Thanks for any help you can provide.

 

[John Spencer]

This would be fairly complex. For instance,
123 N Street is a valid address on N Street
123 N Western Ave is a valid address on North Western Avenue.

The easiest solution would be to break the address at the first space to get
the street name but that would give problems with addresses like
123 1/2 Poppy Ave

You can use the Val function to get the number value.
So, you could sort by
TheStreet: Mid(Address,Instr(1,Address," ")+1)
TheNumber: Val(Address)

If you need the ability to do this, the best solution might be to add fields
to store the street address pieces. Fields such as
AddressNumber
UnitNumber (Apt 1, Suite 500, etc.)
StreetName
StreetType (Avenue, Street, Court, Lane, etc.)
StreetQuadrant (North, South, East, Northeast, etc.)

Then you could combine these into an address when needed.

If you don't (or can't) do the above, then you would probably need to write
a custom function to parse the address into the pieces. The function would
need to adjust for North, South, etc. and other things in the address you
don't want to use in the sorting

Bottom Line, there is no easy solution.

 

[Guest]

Thanks John. . .
I had been considering all the points you made and it seems like trying to
make it simplier is more complicated. Very valid points made. I will try
your code and see if it comes close to my needs. Fortunately, I don't have a
very large database to deal with.

Appeciate your response!

 

[Guest]

Well I couldn't get the code to work that you supplied but perhaps that is
something I did wrong. In the meantime, I standardized the address format in
that I added leading spaces to the numbers so they are all the same length.
Then I added the 'mid(address,7)' to the sort criteria directly in the popup
box for the report sort properties.
It works "good enough" for what I need it to do and it also sorts properly
in the table view. If you know of any way I can format the input field
(i.e., mask) so that it automatically adds these leading spaces or requires
the person inputting the data to add them, it might make it a little easier
than to have to go back and edit the data all the time.

Again, your help is greatly appreciated.

Not as 'Perplexed' as before.