How to separate an address field into three fields access query

[bigtbone]

I have received an access 2000 database that has a table with the city, state and zip in one field. I need to break the field up into a separate fields for city, state and zip using a query. The data is not consistant. Here are examples of the data I have. I understand I may have to manually adjust some records but would like access to do most of the work.

Seattle, WA 98104-1529
Walla Walla, WA 99362
Grandview WA 98930
Boise, ID 83703
Boise ID 83713
Coeur d' Alene ID 83814
Grants Pass OR 97527-9128

 

[Remou]

From the look of the sample data, it seems that you could get quite far with a table of states:

WA
ID
OR

and so on.

Then a query:

UPDATE Table, State 
SET Table.Address1 = Mid([Address],1,InStr([Address]," " & [State].[State] & " ")), 
Table.State = [State].[State], 
Table.Zip = Mid([Address],InStr([Address]," " & [State].[State] & " ")+4)
WHERE ((InStr([Address]," " & [State].[State] & " ")>"0"));

http://lessthandot.com