Solved

extract text from string

Posted on 2013-12-06
19
538 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
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.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
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…

828 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