splitting U.S. address for a small number of records
I just had to do this for a database that someone else had started. Pat's absolutely correct, it is a bear of a problem. If your data is consistent, then it will make your life MUCH easier. I will share what I have learned. I worked from the back to the front and used append/update queries one after the other. (Visually checking the data. GROAN!)
First, try to make sure that every address has a zip code (if not, type them in) and that the zip codes are the same format. Mine were all 5 digits:
zip: IIf([address] Is Null,"",Right$([address],5))
street: IIf([address] Is Null,"",Trim(Left$([address],Len([address])-6)))
Next, is the state. If it is in there as 2 characters, then do the same thing as above, for those 2 characters. If the state is spelled out, then you are in trouble. See below to see how I split the city off.
Now, for the city. I scratched my head over this one for a while. What I ended up doing was to put a semi-colon ";" before each city. Then, I ran two more queries, one to pull that off and one to remove it.
IIf([address] Is Null,Null,Right$([address],Len([address])-InStr(1,[address],";")))
Trim(Left$([address],InStr(1,[address],";")-1)) if it was not null.
The other thought that I had was to look for the last space and break off the city there, then look for strange endings (like 123 Forest Road Mount), and then pull off manually the strange (MOUNT) part.
HTH (If you are already done, then maybe this will help somebody else.)
I don't know enough about addresses outside of the U.S., but the main thing would be if the data is consistent.
