Solved

# Extract City from Address, City, State

Posted on 2007-04-03
Medium Priority
529 Views
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
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

LVL 67

Expert Comment

ID: 18847374
why not split it?

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

LVL 67

Expert Comment

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

TheSloath earned 1000 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

LVL 67

Assisted Solution

sirbounty earned 1000 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

ID: 18847395

try

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

LVL 9

Expert Comment

ID: 18847407
Very slick Cap1 :-)
0

Author Comment

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

ID: 18847562
Thanx. :^)
0

## Featured Post

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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 …
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…
###### Suggested Courses
Course of the Month10 days, 12 hours left to enroll