Solved

How to split a string in a Microsoft Access Database

Posted on 2016-09-24
7
42 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

776 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