Solved

Extract City from Address, City, State

Posted on 2007-04-03
8
470 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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now