Solved

Extract City from Address, City, State

Posted on 2007-04-03
8
472 Views
Last Modified: 2012-05-05
Microsoft Access Query - I have a list of addresses that import into a table like the following example (Address , City , State):

2785 SUNSHINE WAY , KINSTON , NC

I need to extract the city and was able to get
KINSTON , NC with --
Mid([Ship],Len(Left([ship],InStr(1,[ship],",",0)-2))+4)

How do I take off the _,NC??
0
Comment
Question by:mlaurin
8 Comments
 
LVL 67

Expert Comment

by:sirbounty
ID: 18847374
why not split it?

address="2785 SUNSHINE WAY , KINSTON , NC"
strTemp=split(address,",")
city=strTemp(1)
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18847383
This uses your code to get at just the city...

 Trim(Left(Mid(a,Len(Left(a,InStr(1,a,",",0)-2))+4),instr(Mid(a,Len(Left(a,InStr(1,a,",",0)-2))+4),",")-1))
0
 
LVL 9

Accepted Solution

by:
TheSloath earned 250 total points
ID: 18847387
Left$(Mid([Ship],Len(Left([ship],InStr(1,[ship],",",0)-2))+4), Instr(1, Mid([Ship],Len(Left([ship],InStr(1,[ship],",",0)-2))+4), ",") - 1)
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 67

Assisted Solution

by:sirbounty
sirbounty earned 250 total points
ID: 18847389
(where a is your [Ship], of course)

 Trim(Left(Mid([Ship],Len(Left([Ship],InStr(1,[Ship],",",0)-2))+4),instr(Mid([Ship],Len(Left([Ship],InStr(1,[Ship],",",0)-2))+4),",")-1))
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18847395

try

city = Trim(Split(ship, ",")(1))
0
 
LVL 9

Expert Comment

by:TheSloath
ID: 18847407
Very slick Cap1 :-)
0
 

Author Comment

by:mlaurin
ID: 18847453
Sirbounty and TheSlouth - both of yours work great!

Capricorn1 - hoping to make your more simplistic solution work.  I am doing this in a new column in a query:

City2: Trim(Split([ship], ",")(1))

Get an error message that says "Invalid Parentheses".
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18847562
Thanx. :^)
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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 …

775 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