how do i separate an address field into three fields Access 2007

K

kb

I have a table with one address field of street address, city, state and zip
and need to split them into 4 fields. Is there sql I can use in an update
query to do this? I used one to split the name field into two fields --
Left([fullname], InStr([fullname], " ") - 1) and Mid([fullname],
InStr([fullname], " ") + 1) -- but can't quite figure out how to edit this
for the address. There are commas after the street address and city value.
Access 2007. Thank you so much!! kb
 
K

KARL DEWEY

First backup the database. You need to do it in multiple updates due to the
mixture of ways people put addresses.
1111 North 2nd Street, Way City, Rhode Island, 03333-1234
1111 Second Street North, Way City, RI, 03333

First determine those that have zip-plus by checking
Left(Right([YourField],5),1) with "-" as criteria. Update Zip with
Right([YourField],9) and update YourField with Left([YourField],
Len([YourField])-10) to remove zip. Next pass Update Zip with
Right([YourField],5) and update YourField with Left([YourField],
Len([YourField])-6) for Zip Is Null to remove zip and not update those
already updated.

Continue riping parts from the right of the address fields.
 
J

Jeff Boyce

If your "addresses" are formatted/entered EXACTLY the same, you can look to
the Mid(), Left(), Right() and Instr() functions to help you parse the
longer string into the four parts.

And I do mean "EXACTLY"!

Can you guarantee the commas for every string?

Do some addresses have zipcodes, some have zip+4, and some have postal codes
(e.g., Canadian, UK, ...)?

You may have to do the best you can with Access, then rely on USB (using
someone's brain).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
K

kb

Thank you! I managed the zip values, and it worked! I *moved* state in a
brute force manner (same state), but can't quite figure out how to now
separate the street address and city. The original field is stripped of
everything but those. They are typed the same way but some cities are two
words... ?? thanks :) kb

KARL DEWEY said:
First backup the database. You need to do it in multiple updates due to the
mixture of ways people put addresses.
1111 North 2nd Street, Way City, Rhode Island, 03333-1234
1111 Second Street North, Way City, RI, 03333

First determine those that have zip-plus by checking
Left(Right([YourField],5),1) with "-" as criteria. Update Zip with
Right([YourField],9) and update YourField with Left([YourField],
Len([YourField])-10) to remove zip. Next pass Update Zip with
Right([YourField],5) and update YourField with Left([YourField],
Len([YourField])-6) for Zip Is Null to remove zip and not update those
already updated.

Continue riping parts from the right of the address fields.

--
KARL DEWEY
Build a little - Test a little


kb said:
I have a table with one address field of street address, city, state and zip
and need to split them into 4 fields. Is there sql I can use in an update
query to do this? I used one to split the name field into two fields --
Left([fullname], InStr([fullname], " ") - 1) and Mid([fullname],
InStr([fullname], " ") + 1) -- but can't quite figure out how to edit this
for the address. There are commas after the street address and city value.
Access 2007. Thank you so much!! kb
 
K

kb

Yes, I have ensured the addresses are consistent -- no punctuation other than
a comma between street address and city, and a comma between city and state.
Is there a way to use these functions looking for the comma as the break
point? thanks :) kb

Jeff Boyce said:
If your "addresses" are formatted/entered EXACTLY the same, you can look to
the Mid(), Left(), Right() and Instr() functions to help you parse the
longer string into the four parts.

And I do mean "EXACTLY"!

Can you guarantee the commas for every string?

Do some addresses have zipcodes, some have zip+4, and some have postal codes
(e.g., Canadian, UK, ...)?

You may have to do the best you can with Access, then rely on USB (using
someone's brain).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

kb said:
I have a table with one address field of street address, city, state and
zip
and need to split them into 4 fields. Is there sql I can use in an update
query to do this? I used one to split the name field into two fields --
Left([fullname], InStr([fullname], " ") - 1) and Mid([fullname],
InStr([fullname], " ") + 1) -- but can't quite figure out how to edit this
for the address. There are commas after the street address and city value.
Access 2007. Thank you so much!! kb
 
K

KARL DEWEY

You mentioned it in your orignal post --
Left([fullname], InStr([fullname], " ") - 1) but for street part use comma
like --
Left([fullname], InStr([fullname], ",") - 1)

and city - Right([fullname], Len([fullname]) - InStr([fullname], ","))

Use the Trim function to remove leading spaces.

--
KARL DEWEY
Build a little - Test a little


kb said:
Yes, I have ensured the addresses are consistent -- no punctuation other than
a comma between street address and city, and a comma between city and state.
Is there a way to use these functions looking for the comma as the break
point? thanks :) kb

Jeff Boyce said:
If your "addresses" are formatted/entered EXACTLY the same, you can look to
the Mid(), Left(), Right() and Instr() functions to help you parse the
longer string into the four parts.

And I do mean "EXACTLY"!

Can you guarantee the commas for every string?

Do some addresses have zipcodes, some have zip+4, and some have postal codes
(e.g., Canadian, UK, ...)?

You may have to do the best you can with Access, then rely on USB (using
someone's brain).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

kb said:
I have a table with one address field of street address, city, state and
zip
and need to split them into 4 fields. Is there sql I can use in an update
query to do this? I used one to split the name field into two fields --
Left([fullname], InStr([fullname], " ") - 1) and Mid([fullname],
InStr([fullname], " ") + 1) -- but can't quite figure out how to edit this
for the address. There are commas after the street address and city value.
Access 2007. Thank you so much!! kb
 
K

kb

Thank you! I'm still relatively new to this :) I tried the comma like below
and the result was unexpected, but I think I did not use a number.

Adding the trim function would be directly with the field name, or outside
of the left() and instr()?

thanks so much! :) kb

KARL DEWEY said:
You mentioned it in your orignal post --
Left([fullname], InStr([fullname], " ") - 1) but for street part use comma
like --
Left([fullname], InStr([fullname], ",") - 1)

and city - Right([fullname], Len([fullname]) - InStr([fullname], ","))

Use the Trim function to remove leading spaces.

--
KARL DEWEY
Build a little - Test a little


kb said:
Yes, I have ensured the addresses are consistent -- no punctuation other than
a comma between street address and city, and a comma between city and state.
Is there a way to use these functions looking for the comma as the break
point? thanks :) kb

Jeff Boyce said:
If your "addresses" are formatted/entered EXACTLY the same, you can look to
the Mid(), Left(), Right() and Instr() functions to help you parse the
longer string into the four parts.

And I do mean "EXACTLY"!

Can you guarantee the commas for every string?

Do some addresses have zipcodes, some have zip+4, and some have postal codes
(e.g., Canadian, UK, ...)?

You may have to do the best you can with Access, then rely on USB (using
someone's brain).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table with one address field of street address, city, state and
zip
and need to split them into 4 fields. Is there sql I can use in an update
query to do this? I used one to split the name field into two fields --
Left([fullname], InStr([fullname], " ") - 1) and Mid([fullname],
InStr([fullname], " ") + 1) -- but can't quite figure out how to edit this
for the address. There are commas after the street address and city value.
Access 2007. Thank you so much!! kb
 
K

KARL DEWEY

Completely outside like ---
Trim(Right([fullname], Len([fullname]) - InStr([fullname], ",")) )

--
KARL DEWEY
Build a little - Test a little


kb said:
Thank you! I'm still relatively new to this :) I tried the comma like below
and the result was unexpected, but I think I did not use a number.

Adding the trim function would be directly with the field name, or outside
of the left() and instr()?

thanks so much! :) kb

KARL DEWEY said:
You mentioned it in your orignal post --
Left([fullname], InStr([fullname], " ") - 1) but for street part use comma
like --
Left([fullname], InStr([fullname], ",") - 1)

and city - Right([fullname], Len([fullname]) - InStr([fullname], ","))

Use the Trim function to remove leading spaces.

--
KARL DEWEY
Build a little - Test a little


kb said:
Yes, I have ensured the addresses are consistent -- no punctuation other than
a comma between street address and city, and a comma between city and state.
Is there a way to use these functions looking for the comma as the break
point? thanks :) kb

:

If your "addresses" are formatted/entered EXACTLY the same, you can look to
the Mid(), Left(), Right() and Instr() functions to help you parse the
longer string into the four parts.

And I do mean "EXACTLY"!

Can you guarantee the commas for every string?

Do some addresses have zipcodes, some have zip+4, and some have postal codes
(e.g., Canadian, UK, ...)?

You may have to do the best you can with Access, then rely on USB (using
someone's brain).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table with one address field of street address, city, state and
zip
and need to split them into 4 fields. Is there sql I can use in an update
query to do this? I used one to split the name field into two fields --
Left([fullname], InStr([fullname], " ") - 1) and Mid([fullname],
InStr([fullname], " ") + 1) -- but can't quite figure out how to edit this
for the address. There are commas after the street address and city value.
Access 2007. Thank you so much!! kb
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top