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])
," ")+1,[names]," ")),Right([names],Len([nam
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:
And that doesn't show anything at all. What do I need to do differently?
Should indeed return the last two letters of the field.
In the query grid, it would look like this:
Make sure that you have Names in square brackets