Stripping city from address field and placing into city field

[bird lover]

I use xp and access 2003. I have many fields with address, but don't know
how to remove the city infofmation and place into [city]. I prefer query
solutions. I do not have programming, vbasic or sql knowledge. Samples are
below:

Field [address] contains numerous entries similar to
25 Main St, Brooklyn, 11201
100 Broadway, Anywhere, 11205

Previously, I was given Mid([ADDRESS],1,(InStr(1,[ADDRESS],",",1)-1)) which
I used to insert into field [street] which I used in an update field for
[Street]

Now, I’d like to insert the City into field [city]. Help requested on
syntax that I can paste into an update query.

 

[John W. Vinson]

I use xp and access 2003. I have many fields with address, but don't know
how to remove the city infofmation and place into [city]. I prefer query
solutions. I do not have programming, vbasic or sql knowledge. Samples are
below:

Field [address] contains numerous entries similar to
25 Main St, Brooklyn, 11201
100 Broadway, Anywhere, 11205

Previously, I was given Mid([ADDRESS],1,(InStr(1,[ADDRESS],",",1)-1)) which
I used to insert into field [street] which I used in an update field for
[Street]

Now, I’d like to insert the City into field [city]. Help requested on
syntax that I can paste into an update query.

If you can RELIABLY count on the format - with exactly two commas and the city
name between them - you can use

Trim(Mid([Address], InStr([Address], ",") + 1, InStr(InStr([Address], ","),
[Address], InStr([Address], ",") - 1))

This uses the optional first parameter of InStr specifying where in the string
to start. See the VBA online help for InStr.

 

[Paul Shapiro]

But the key word in John's answer is RELIABLY. I think if you look carefully
at your data, it is very unlikely to be reliably consistent.
"25 Main St, Brooklyn, 11201" could also be entered as "25 Main St, Brooklyn
11201", without the trailing comma, or maybe sometimes "25 Main St Brooklyn
11201", without any commas. This is actually the US Post Office's specified
format, without the comma. What about "25 Main St, Apt 6, Brooklyn NY
11201", with an extra comma in the street portion?

I have always had to do this kind of address cleanup semi-manually. Code
like John's will get most of the addresses correctly, but you have to find a
way to identify addresses that don't match your formula. If it's all US
addresses, for example, you can extract the state field as well and flag any
addresses where what you get for the state is not in the list of actual
states. I would expect 5-20% errors depending on the degree of variability
in your data.

If you have too many addresses to do the manual work, there are services
that clean addresses pretty reliably using the post office databases. The
last time I used one of them, you still have to check the results, but the
error rate is more like 0.1 - 1% even with highly varying formats. You can
buy address-cleaning software and do the cleanup yourself, but it's
expensive and requires a subscription to stay up to date.

I use xp and access 2003. I have many fields with address, but don't know
how to remove the city infofmation and place into [city]. I prefer query
solutions. I do not have programming, vbasic or sql knowledge. Samples
are
below:

Field [address] contains numerous entries similar to
25 Main St, Brooklyn, 11201
100 Broadway, Anywhere, 11205

Previously, I was given Mid([ADDRESS],1,(InStr(1,[ADDRESS],",",1)-1))
which
I used to insert into field [street] which I used in an update field for
[Street]

Now, I'd like to insert the City into field [city]. Help requested on
syntax that I can paste into an update query.

If you can RELIABLY count on the format - with exactly two commas and the
city
name between them - you can use

Trim(Mid([Address], InStr([Address], ",") + 1, InStr(InStr([Address],
","),
[Address], InStr([Address], ",") - 1))

This uses the optional first parameter of InStr specifying where in the
string
to start. See the VBA online help for InStr.

 

[John W. Vinson]

I have always had to do this kind of address cleanup semi-manually. Code
like John's will get most of the addresses correctly, but you have to find a
way to identify addresses that don't match your formula.

Absolutely! Thanks for the addition, Paul. This kind of problem pretty much
always requires a USB (Using Someone's Brain) interface.