Query for address field.

[Iwilfix]

I have an address field in my table with street numbers and names
( 248 elm St. , 583 Spring Rd. etc. )
Is it possible to create a query that will put all the street names
in alpabetical order?
Thanks, Jeff V.

 

[Alastair MacFarlane]

Jeff

You will need to extract the street field of the table
twice on a query the first one will have show = false and
sort order ascending with the folloing criteria in the
first column:

Right(Trim([Address]),Len(Trim([Address]))-InStr(1,
[Names]," "))

This criteria returns everything to the right of the first
space using the Instr function and then sort in ascending
order.

I hope this helps!

Alastair

 

[fredsf]

-----Original Message-----
Jeff

You will need to extract the street field of the table
twice on a query the first one will have show = false and
sort order ascending with the folloing criteria in the
first column:

Right(Trim([Address]),Len(Trim([Address]))-InStr(1,
[Names]," "))

This criteria returns everything to the right of the first
space using the Instr function and then sort in ascending
order.

I hope this helps!

Alastair

-----Original Message-----
I have an address field in my table with street numbers and names
( 248 elm St. , 583 Spring Rd. etc. )
Is it possible to create a query that will put all the street names
in alpabetical order?
Thanks, Jeff V.
.

.
Is there a way to pull off the apartment number? For

example the address reads "123 Main Street #45". The
code below --> apt2: Mid([street2],InStr([street2]," #")+2)
pulls the correct apartment number. But the problem if
there is no apartment number (678 South Street). The
apartment number should be blank, but the field
reads "South Street".

Thanks for any feed back.

 

[John Vinson]

Is there a way to pull off the apartment number? For

example the address reads "123 Main Street #45". The
code below --> apt2: Mid([street2],InStr([street2]," #")+2)
pulls the correct apartment number. But the problem if
there is no apartment number (678 South Street). The
apartment number should be blank, but the field
reads "South Street".

It REALLY sounds like you should split the field up in your table
design. It's a heck of a lot easier to concatenate three fields, e.g.

[StreetNo] & " " & [StreetName] & (" #" + [AptNo])

than to take it apart. Not only will you have problems with the
apartment number - what if an address is entered as 342 Main St. Apt.
3, or 342 Main St No. 3; but what about addresses like

1912 1/2 S. Ohio
The Birches
312 A Evert Ave.

 

[Guest]

Any hints on how to do that, and may generate an even/odd field at the same time?