Solved

extract text from string

Posted on 2013-12-06
19
552 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

705 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