Link to home
Start Free TrialLog in
Avatar of Info Tech
Info TechFlag for United States of America

asked on

extract text from string

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?
Avatar of mbizup
mbizup
Flag of Kazakhstan image

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 []
Avatar of Info Tech

ASKER

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?
The city without the state would then be:

City: trim(Left([Names], Len([Names]) - 2))
SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
City: trim(Left([Names], Len([Names]) - 2))
returns the exact same string as the original, both city and state.
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)
State:Right(Trim([names]),2)

worked!  Thank you!! Now how can I get just the city, whether it's one word or two?
try trim()


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

City: Left([name], instrrev(trim([name]), " ")-1)
Using Trim again:


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

Or this:

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

returns

User generated image
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
maybe [ADDR3] is only one word or Null for that particular record

try, copy and paste

City: Left([ADDR3],InStrRev(Trim([ADDR3]), & " ", " ")-1)
"The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier."
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
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
place the criteria under the field [ADDr3]

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


   Is Not Null
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I had the is not null in the correct place.  

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

worked!  Thank you both!!  :)
Glad to help..

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


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