[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Parsing City and State From One Field

Posted on 2011-09-22
5
Medium Priority
?
274 Views
Last Modified: 2012-05-12
I've read all of the post and I don't think this one has been answered.  I have a field that has CITY ST.  I need to break them out into two fields.  No commas just a space between CITY and ST.  I've already done the ST as that was the easy part.  Also, the solution needs to be something I can put into a query.  Thanks for your help.
0
Comment
Question by:DocB1973
  • 3
  • 2
5 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36582734

try this query

select field,left([field],instr([field]," ")-1) as City, mid([field],instr([field]," ")+1) as State
from tableName
0
 

Author Comment

by:DocB1973
ID: 36582785
Thank for the quick reply.  It handles everything except when the city has more than one name.  Exp: Grand Rapids MI.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 36582817

ok try this

select field,left([field],instrrev([field]," ")-1) as City, mid([field],instrrev([field]," ")+1) as State
from tableName




for other fomats you will then need a UDF to do the parsing
0
 

Author Comment

by:DocB1973
ID: 36582941
Fantasic!  Works like a charm.
0
 

Author Closing Comment

by:DocB1973
ID: 36582951
Great job for something you think you know how to do but it turns out it's more difficult than you think.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

608 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