Solved

extract text from string

Posted on 2013-12-06
19
519 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
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now