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])
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?
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])
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?
Microsoft Access
Last Comment
mbizup
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))
City: trim(Left([Names], Len([Names]) - 2))
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
City: trim(Left([Names], Len([Names]) - 2))
returns the exact same string as the original, both city and state.
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
State: Right(Trim([Names]),2)
or this:
State: Right(Replace(Replace(Trim
ASKER
State:Right(Trim([names]),
worked! Thank you!! Now how can I get just the city, whether it's one word or two?
worked! Thank you!! Now how can I get just the city, whether it's one word or two?
try trim()
State:Right(Trim([names]),
City: Left([name], instrrev(trim([name]), " ")-1)
State:Right(Trim([names]),
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: trim(Left(Trim([Names]), Len(Trim([Names])) - 2))
Or this:
City: Left(Trim([Names]), Len(Trim([Names])) - 2)
ASKER
City: Left([ADDR3],InStrRev(Trim
returns
![screenshot]()
returns
ASKER
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
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
try, copy and paste
City: Left([ADDR3],InStrRev(Trim
ASKER
"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
check the field [ADDR3] is not null for the particular record
City: Left([ADDR3],InStrRev(Trim
criteria Is Not Null
City: Left([ADDR3],InStrRev(Trim
check the field [ADDR3] is not null for the particular record
City: Left([ADDR3],InStrRev(Trim
criteria Is Not Null
ASKER
It's a query of 12,000 records, and of those only 6 are null in ADDR3.
City: Left([ADDR3],InStrRev(Trim
criteria Is Not Null
gives me the complex expression error
City: Left([ADDR3],InStrRev(Trim
criteria Is Not Null
gives me the complex expression error
place the criteria under the field [ADDr3]
[ADDR3] | City: Left([ADDR3],InStrRev(Trim
Is Not Null
[ADDR3] | City: Left([ADDR3],InStrRev(Trim
Is Not Null
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I had the is not null in the correct place.
City: trim( Replace([Names], " " & Right(Trim([names]),2) , ""))
worked! Thank you both!! :)
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) , "")),",", "")
Also If this field may contain comma separators between city and state:
City: Replace(trim( Replace([Names] & "", " " & Right(Trim([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 []