Solved

extract text from string

Posted on 2013-12-06
19
529 Views
Last Modified: 2013-12-06
In Access 2007, I have a field with city and state combined ("Hollywood CA").  I need to separate the state into it's own field.  According to Microsoft (http://support.microsoft.com/kb/286238), the proper way to do that is:

Original Entry in [Names]: "John Doe" or "John P. Doe"
Returned by Expression: Doe
Expression: Expr: IIf(InStr(InStr([names]," ")+1,[names]," ")<>0, Right([names],Len([names])-InStr(InStr([names]," ")+1,[names]," ")),Right([names],Len([names])-InStr([names]," ")))

The problem is some of my city names are two words, so when I try this formula it only returns states for the fields that have a two-word city.  ("Los Angeles CA") The fields with just a one-word city and state ("Hollywood CA") don't return any results.  

I also tried to just simplify it and pull the last 2 characters from the field:

Right([names],2)

And that doesn't show anything at all.  What do I need to do differently?
0
Comment
Question by:fallriverelectric
  • 8
  • 6
  • 5
19 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39702407
Right([names],2)

Should indeed return the last two letters of the field.

In the query grid, it would look like this:

State: Right([Names],2)



Make sure that you have Names in square brackets []
0
 

Author Comment

by:fallriverelectric
ID: 39702413
I did have it correct, I just didn't post the expression name here.  It's possible that there are trailing spaces in the field, I'm not sure.  Would there be a way to eliminate them from showing if they were there?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39702416
The city without the state would then be:

City: trim(Left([Names], Len([Names]) - 2))
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 39702418
try trim()


State:Right(Trim([names]),2)
0
 

Author Comment

by:fallriverelectric
ID: 39702421
City: trim(Left([Names], Len([Names]) - 2))
returns the exact same string as the original, both city and state.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39702423
Trialing spaces would be odd, but try this:


State: Right(Trim([Names]),2)


or this:

State: Right(Replace(Replace(Trim([Names]), chr(13),""), chr(10),""),2)
0
 

Author Comment

by:fallriverelectric
ID: 39702426
State:Right(Trim([names]),2)

worked!  Thank you!! Now how can I get just the city, whether it's one word or two?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39702429
try trim()


State:Right(Trim([names]),2)

City: Left([name], instrrev(trim([name]), " ")-1)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39702432
Using Trim again:


City: trim(Left(Trim([Names]), Len(Trim([Names])) - 2))

Or this:

City: Left(Trim([Names]), Len(Trim([Names])) - 2)
0
 

Author Comment

by:fallriverelectric
ID: 39702441
City: Left([ADDR3],InStrRev(Trim([ADDR3])," ")-1)

returns

screenshot
0
 

Author Comment

by:fallriverelectric
ID: 39702445
City: trim(Left(Trim([Names]), Len(Trim([Names])) - 2))

and

City: Left(Trim([Names]), Len(Trim([Names])) - 2)

return the same error as the screenshot previous
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39702449
maybe [ADDR3] is only one word or Null for that particular record

try, copy and paste

City: Left([ADDR3],InStrRev(Trim([ADDR3]), & " ", " ")-1)
0
 

Author Comment

by:fallriverelectric
ID: 39702452
"The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier."
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39702456
oops too many commas


City: Left([ADDR3],InStrRev(Trim([ADDR3]) & " ", " ")-1)



check the field [ADDR3] is not null for the particular record



City: Left([ADDR3],InStrRev(Trim([ADDR3])," ")-1)

criteria  Is Not Null
0
 

Author Comment

by:fallriverelectric
ID: 39702463
It's a query of 12,000 records, and of those only 6 are null in ADDR3.  

City: Left([ADDR3],InStrRev(Trim([ADDR3])," ")-1)

criteria  Is Not Null

gives me the complex expression error
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39702470
place the criteria under the field [ADDr3]

  [ADDR3]    |  City: Left([ADDR3],InStrRev(Trim([ADDR3])," ")-1)


   Is Not Null
0
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 39702473
City: trim( Replace([Names] & "", " " & Right(Trim([names] & ""),2) , ""))
0
 

Author Comment

by:fallriverelectric
ID: 39702476
I had the is not null in the correct place.  

City: trim( Replace([Names], " " & Right(Trim([names]),2) , ""))

worked!  Thank you both!!  :)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39702478
Glad to help..

Also If this field may contain comma separators between city and state:


City: Replace(trim( Replace([Names] & "", " " & Right(Trim([names] & ""),2) , "")),",", "")
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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

776 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