Solved

How to split a string in a Microsoft Access Database

Posted on 2016-09-24
7
44 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

839 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