Solved

Parsing City and State From One Field

Posted on 2011-09-22
5
225 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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero earned 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now