Address Fields

C

Catherine

I have an address field that I would like to separate into 2 fields - one for
just the street number and the other for just the street name. How should I
set up this query?
My objective is to be able to filter a form by street name at anytime. If
there is an easier way than the query I mentioned, please adivse.
Thanks in advance for you assistance.
 
D

Douglas J. Steele

Splitting into number and address can sometimes be problematic if you've got
apartment information and the like.

If all you're trying to do is create a query, you can use a LIKE clause. To
look for Main St., for instance, you'd use WHERE Address LIKE "*Main*"

You can put LIKE "*" & [What street?] & "*" in the Criteria cell in the
graphical query designer and then you'd only have to type Main in the prompt
that appears.
 
J

John Vinson

I have an address field that I would like to separate into 2 fields - one for
just the street number and the other for just the street name. How should I
set up this query?
Click to expand...

Carefully.

Here are some real, valid addresses with the resulting number and
street:

1812 1/2 S. Utica => 1812 1/2; S. Utica
Ballacraine => Ballacraine; <null>
33-35 Beal Court => 33-35; Beal Court
3321 San Jacinto => 3321; San Jacinto
My objective is to be able to filter a form by street name at anytime. If
there is an easier way than the query I mentioned, please adivse.
Thanks in advance for you assistance.
Click to expand...

A FIRST PASS - one which will fail in the first two examples above -
would be to add two new text fields, AddressNo and Street. Run an
update query updating AddressNo to

Left([Address], InStr([Address], " ") - 1)

and Street to

Trim(Mid([Address], InStr([Address], " ")))

John W. Vinson[MVP]
 
Top