?
Solved

How to split a string in a Microsoft Access Database

Posted on 2016-09-24
7
Medium Priority
?
82 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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 …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

800 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