sorting address/streets

[kathy]

I want to do a query to find records of people living on a
certain street. Unfortunately my field does not seperate
street number from street name. How can a query a
particular street or how can I easily separate the field
into two (one street # , and one street name)? Please
help.. thanks

 

[Rob]

If you have previously been using one field to store data
in this may work. You will need to use an Update Query.
First, add two new fields to the table,BldNumber and
StreetName, with DataType Text, and then save this table.

Create a new query, and add the table to the query.
Change the query type from Select (which is the default)
to Update. Drag the first field, BldNumber, to the query
grid. Then, in the 'Update to' field, type this:

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

Repeat the process with the second field, StreetName, but
the 'Update to' field will now be:

Mid([Address],Instr([Address]," ")+1)

You can then run this query, and the BldNumber field
should now contain everything that was to the left of the
first space in the original field, and the StreetName
field should have everything after this.

I recommend that you work with a copy of your data when
working with code until you know the code works as
expected.

Hope this helps,
Rob

 

[John Spencer (MVP)]

In the query grid

Field: YourAddressField
Table: YourTable
Criteria: Like "*" & [Enter Street Name] & "*"

This will match any address that contains what you enter as a street name. The
problem is that "Ada" will match Adams Street and Ada Street and Adamson Avenue
plus Madam Street. You can make it a bit more exact doing the following.

Field: SearchThis: " " & [YourTable].[YourAddressField] & " "
Table: YourTable
Criteria: Like "* " & [Enter Street Name] & " *"

This would find matches where the street name was at the end of the address
field or was followed by a space. So entering ADAMS would match
2 Adams Street
13 Adams Avenue
5 Adams
But not
12 Adamson Street

Hope this helps.

IF you are entering this directly into the query.

Criteria: Like "* Adams *"