Split city, state zip

[Guest]

I need to split one column into three city, state, zip
Right now they are all in one in the following formats:

city, state zip
sometimes the state is spelled out sometimes abreviated, the zip sometimes
has a suffix on it, ie 94241-1234

Let me know what you think is the best way to deal with it?? I think if I
can separate it first by "," (left) and next by a 'space' from the right it
might work, I just can't get it to work right. Thanks for your help

 

[Guest]

I would do it in multiple steps. Add the new fields to the table.

First the Zip - Update the Zip field with Trim(Right([YourOldField],10)

Then build select query for Zip field to pull all that Asc(left([ZIP],1) <
48 and > 57 to pull any that have text as they did not have Zip Plus. Change
the select query to update and update with Right([ZIP],5) to remove the text.

Create a lookup table listing state names and abreviations. A second field
for the two letter abreviations. Create a select query with both tables and
criteria for YourOldField like this -- Like "*" &
[StateLookUpTable].[Name] & "*"
Change to update query and update State with [StateLookUpTable].[Abreviation]

For City use Left([YourOldField], Len([YourOldField]) - (Len([ZIP])+3))
This will result in those records that had state names having the city and
state name missing last 2 characters. Create a select query with State
joined to StateLookUpTable on the abreviation. Use a criteria on State like
--
Like "*" &
Left([StateLookUpTable].[Name],(Len([StateLookUpTable].[Name])-2))
This will pull the cities that have the state name part.
Update to remove the state name part --
Trim(left([City], Len([City]) - Len([StateLookUpTable].[Name])-2))