rye004
asked on
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
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
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.
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.
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
?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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Nice solution Joe ...
ET
ET
ASKER
This was very helpful, thanks!
You are welcome.
You are in So Cal ?
You are in So Cal ?
Lookup up the Split() Function ...