How to split a string in a Microsoft Access Database

I am working with a Microsoft Access Database.  There is a table which contains a column called 'City'.  This column contains the City, State and Zip in a comma delimited format.  I would like to write a query in Access to show the City, State and ZIP as separate columns.  These are all locations in the US.

I wanted to see if anyone had any suggestions on how to accomplish this.  I have attached a screenshot of the column as a sample.
cityscreenshot.jpg
rye004Asked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Split() doesn't seem to work in a query cell ... so

Public Function mGetCityStateZip(sPart As String, sLocation) As String

    Select Case sPart
        Case "City"
            mGetCityStateZip = Split(sLocation, ",")(0)
        Case "State"
            mGetCityStateZip = Split(sLocation, ",")(1)
        Case "Zip"
            mGetCityStateZip = Split(sLocation, ",")(2)
    End Select

End Function

Split()

Result
1
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Looks like So Cal to me. Where is 91730 :-)
Lookup up the Split() Function ...
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
https://msdn.microsoft.com/en-us/library/office/gg278528(v=office.15).aspx

It's been a while since I used it.  You may have to create a Public Function to parse out each piece and return it to the query.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Here we go

?Split("Rancho Cucamonga, CA, 91730",",")(0)
Rancho Cucamonga

?Split("Rancho Cucamonga, CA, 91730",",")(1)
 CA
?Split("Rancho Cucamonga, CA, 91730",",")(2)
 91730

I think you can do each of these in a query cell
0
 
Eric ShermanAccountant/DeveloperCommented:
Nice solution Joe ...

ET
0
 
rye004Author Commented:
This was very helpful, thanks!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You are welcome.

You are in So Cal ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.