[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

extract text from string

Posted on 2013-12-06
19
Medium Priority
?
563 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 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 1000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

649 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