Parcing a field in Microsoft Access


I have a address filed which contains data like this - Jacksonville, FL 32205-9351
I like to be able to separate it into city, state and zip fields.
Sometimes this filed contains only the address like - David Lipscomb University (without city, state and zip)
FYI I do not know VB. Is there anything I can do in Access.

Many thanks in advance.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leigh PurvisDatabase DeveloperCommented:
If there's no consistency to it at all - then you're pretty knackered.
If you can identify components of the text in a regular, pre-defined manner then it can be parsed out to different fields.

The first address you give as an example has no initial address component - and the latter nothing but that.
If you can imply that no comma means it's just the first line - then so be it - but where would that go?
PAPA1964Author Commented:
Yes, unfortunately there is no consistency. If the field has just the address like this - David Lipscomb University , I want this to remain in the field it is now and the other one - Jacksonville, FL 32205-9351 should be separated into different fields.

Leigh PurvisDatabase DeveloperCommented:
When not doing this in code - it can get ugly in an SQL statement...
For example just the city might be

UPDATE YourTable Set city = IIF(Instr(Nz(YourField),",")>0,Left(YourField,Instr(YourField,",")-1),Null)
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

PAPA1964Author Commented:
Thanks for your response. If you have the VB code please do send me, I will have some one help me with that.

Thanks again.
Leigh PurvisDatabase DeveloperCommented:
Well - do you have the tablename and all field names in question.
Then, you never know, the air code may just run ;-)
PAPA1964Author Commented:
Yes, I do know the table name and field names. If this works, I need to clean up other fields also.

If you can say that the city is always comma separated from the State abbreviation which is always two letters, and anything thereafter is alway the zip code, then you could try this, presuming the table Adresses has the field Address and three blank text fields, City, State, and Zip.  Always work from a backup:

Insert Into Addresses (City, State, Zip) SELECT
Left(address,Instr(address,","),-1 as City,
Mid(address,instr(address,",")+2,2) as State,
Mid(address,instr(address,",")+5) as Zip
FROM Addresses;
PAPA1964Author Commented:
Yes, there is a field called Addrress. There are already city, state and zip fields with some data for some records. If I try this code will it delete the existing data from these fields?
And as I mentioned earlier, in the address field there is also data like this - 5000 Longleaf Ln (no city, state or zip)
Is this code going to work then?

Yes, it will overwrite anything that is already there.  Rather than do the Insert initially, tryrunning the Query from SELECT on.  This will show you what it will create. If there is no comma in the string, nulls will be inserted in City, State and Zip.  I had to modify the query to cater to the cases where there is no comma:

INSERT INTO Addresses (City, State, Zip) SELECT
IIF(Instr(address,",")=0,Null,Left(address,Instr(address,","),-1) as City,
IIF(Instr(address,",")=0,Null,Mid(address,instr(address,",")+2,2)) as State,
IIF(Instr(address,",")=0,Null,Mid(address,instr(address,",")+5)) as Zip
FROM Addresses;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Thanks, but why the B? What did I not provide?  You may not have liked the limitations in using such a query, but that is caused by the variation in your data, not the query.  
PAPA1964Author Commented:
You are right the answer deserves an 'A' .

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.