Solved

Extract City from Address, City, State

Posted on 2007-04-03
8
509 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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 …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

635 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