Update query nighmare!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Im wondering if its possible to; ok i want to split the field name
'address' because it contains customers street number and street name. for
example;
'22 Austin Street' I want to split it into 2 fields so one field for '22'
and the other for 'Austin Street'. The problem is, i have over 10,000
records and am wanting to do this with every street in my town so every
record. can access update queries deal with lists of diffrent street names
and change them in the appropriate records? I am finding that it only
updates all records to one street name!
this is major and i have to do this because of post codes that are coming
into effect soon and i figure that doing this will help make finding
postcodes easier. should have done this before i started entering records!
HELP! thanks
 
Dear Debba:

As a preliminary question, is it likely going to work to split the address
at the first space in the existing field.

I'll suggest that it may be possible to use a function to do this and yet
leave your data alone as it is. If such a rule suffices, then you won't
have to change the table, but use the functions (StreetNumber() and
StreetName()) to then implement the zip code lookup that is the next step.
Does that also make any sense?

Tom Ellison
 
Thanks Tom, I can kind of understand what you mean, so what your saying is,
leave the field as it is and that its possible to retrieve the postcode for
the exact record using those functions? Would these functions be used in a
parameter query?
Deb.
 
Dear Debba:

I don't see what parameters have to do with this.

Use the InStr() function to find the location of the space.

The Left() function will return what is to the left of the space, that is,
the number:

Left(address, InStr(address, " ") - 1)

The Mid function will return what is to the right of the space:

Mid(address, Instr(address, " ") + 1)

Does that help?

If you have any addresses, even just one, that does not contain at least one
space, then this will give an error and not return any results. We may need
to get more sophisticated, but this is a starting point.

Tom Ellison
 
Im wondering if its possible to; ok i want to split the field name
'address' because it contains customers street number and street name. for
example;
'22 Austin Street' I want to split it into 2 fields so one field for '22'
and the other for 'Austin Street'. The problem is, i have over 10,000
records and am wanting to do this with every street in my town so every
record. can access update queries deal with lists of diffrent street names
and change them in the appropriate records? I am finding that it only
updates all records to one street name!
this is major and i have to do this because of post codes that are coming
into effect soon and i figure that doing this will help make finding
postcodes easier. should have done this before i started entering records!
HELP! thanks
Click to expand...

Well... maybe. It depends on how your addresses are formatted.

If you can COUNT on an address always having a street number, followed
by a blank, followed by a street name, you can add two new fields to
your table, StreetNumber and StreetName. Run an Update query updating
these to

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

and

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

respectively. These expressions will find the first blank in the field
Address and split the value there - "22 Austin Street" will be split
into "22" and "Austin Street".

HOWEVER - "1812 1/2 S. Zenith" (my late grandmother's address, once)
will e split into "1812" - which is wrong - and "1/2 S. Zenith", also
wrong. And my friend Donna's legal address is "Bellacraine" - the post
office grumbled but she gets her mail. This will fail altogether! You
may need to do some careful editing after running the query!

John W. Vinson[MVP]
 
Thankyou both for your help, I will try these, but you are right, the
addresses are all different and contain spaces everywhere and not
nescessarily numbers first, this is why i was creating an update query going
from street to street and then replacing the street name with nothing but
shifting it to another field, leaving the rest of the address behind - my
town isnt that big fortuneately! i was hoping i could list all the streets
at once and replace them but it didnt seem to work.

John Vinson said:
Im wondering if its possible to; ok i want to split the field name
'address' because it contains customers street number and street name. for
example;
'22 Austin Street' I want to split it into 2 fields so one field for '22'
and the other for 'Austin Street'. The problem is, i have over 10,000
records and am wanting to do this with every street in my town so every
record. can access update queries deal with lists of diffrent street names
and change them in the appropriate records? I am finding that it only
updates all records to one street name!
this is major and i have to do this because of post codes that are coming
into effect soon and i figure that doing this will help make finding
postcodes easier. should have done this before i started entering records!
HELP! thanks
Click to expand...

Well... maybe. It depends on how your addresses are formatted.

If you can COUNT on an address always having a street number, followed
by a blank, followed by a street name, you can add two new fields to
your table, StreetNumber and StreetName. Run an Update query updating
these to

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

and

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

respectively. These expressions will find the first blank in the field
Address and split the value there - "22 Austin Street" will be split
into "22" and "Austin Street".

HOWEVER - "1812 1/2 S. Zenith" (my late grandmother's address, once)
will e split into "1812" - which is wrong - and "1/2 S. Zenith", also
wrong. And my friend Donna's legal address is "Bellacraine" - the post
office grumbled but she gets her mail. This will fail altogether! You
may need to do some careful editing after running the query!

John W. Vinson[MVP]
Click to expand...
 
Thankyou both for your help, I will try these, but you are right, the
addresses are all different and contain spaces everywhere and not
nescessarily numbers first, this is why i was creating an update query going
from street to street and then replacing the street name with nothing but
shifting it to another field, leaving the rest of the address behind - my
town isnt that big fortuneately! i was hoping i could list all the streets
at once and replace them but it didnt seem to work.
Click to expand...

You'll have to explain what you mean by that. I have no idea how your
addresses are stored or what you were trying to do!

One POSSIBLE solution would be to create a table with all of your
street names (you'll want this as the rowsource of a combo box anyway,
most likely). You could create a Query joining this streets table to
your address table to find all of the instances of each street name:
you'll need to use a "Non Equi Join", an obscure but often useful
technique:

SELECT yourtable.Address, <any other fields>
FROM yourtable LEFT JOIN Streets
ON yourtable.Address LIKE "*" & Streets.Street & "*"
ORDER BY Streets.Street;

Note that this is a bit of a rough tool: if you have "Second Street"
and "Twenty-second Street" they'll both match "Second", or any other
instance where one street name is embedded in another. You'll also
have problems if you have addresses like "3216 5th Ave." and "3218
Fifth Avenue" - a human can see that these are next-door neighbors but
to Access they're on two unrelated streets.

John W. Vinson[MVP]
 
Hi Debba,

Here's a VBA function that may help. You can call it in a query and it
does a reasonably good job of parsing many address lines (including
John's friend Donna's, but not his grandmother's).

Public Function ParseAddress36(V As Variant, Item As Long) As Variant
'Parses many street addresses, such as
' 123 Chestnut St
' 4540 North Horse Hockey Ln #320
' 5 Broadway
' 201 5th Ave

Dim oRE As Object 'VBScript_RegExp_55.RegExp
Dim oMatches As Object 'VBScript_RegExp_55.MatchCollection
Dim Regex As String
Dim Result As String


ParseAddress36 = Null 'default value


If IsNull(V) Then
Exit Function
End If


Set oRE = CreateObject("Vbscript.Regexp")


'Assemble regular expression to parse address
'Item 0: Optional street number
Regex = "^\s*(\d+[A-Za-z]?\s+)?"
'Item 1: Optional North/South etc.
Regex = Regex & "(North|South|East|West)?"
'Item 2: Street Name (one or more words, or ordinal number, required)
Regex = Regex & "\s*(?:(\b\w+\b.*?)"
'Item 3: Optional Street Type (add more types and variants to list if
needed)
Regex = Regex & "(?:\s+(St|Ln|Bvd|Rd|Dve|Ave|Way|Cr|Tce))?)"
'Item 4: Optional apartment number
Regex = Regex & "\s*?(\s+#?\d+)?\s*$"

With oRE
.Pattern = Regex
.IgnoreCase = True
Set oMatches = .Execute(CStr(V))
End With

If oMatches.Count > 0 Then
ParseAddress36 = Trim(oMatches(0).SubMatches(Item))
End If
End Function
 
Back
Top