Solved

Extract City from Address, City, State

Posted on 2007-04-03
8
483 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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 …

830 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