Solved

Parsing City and State From One Field

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

770 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