?
Solved

How to split a string in a Microsoft Access Database

Posted on 2016-09-24
7
Medium Priority
?
147 Views
Last Modified: 2016-09-25
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
0
Comment
Question by:rye004
  • 5
7 Comments
 
LVL 75
ID: 41814403
Looks like So Cal to me. Where is 91730 :-)
Lookup up the Split() Function ...
0
 
LVL 75
ID: 41814406
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
 
LVL 75
ID: 41814409
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 41814429
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
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41814845
Nice solution Joe ...

ET
0
 

Author Closing Comment

by:rye004
ID: 41814906
This was very helpful, thanks!
0
 
LVL 75
ID: 41814908
You are welcome.

You are in So Cal ?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question