exp vg
asked on
Access - Keep last most component in Last Name field
I have a field Last Name, that may or may not contain more than one entities, separated by a space. I am hoping to retrieve the last most component before the first space going from right to left.
Example:
De La Cruz
Cruz
Guzman Cruz
In all instances, I am hoping to receive
Cruz
I have tried
Right([Last Name],Len([Last Name])-InStr([Last Name],",")-1)
and
IIf([Last Name] Like "* *",Left([Last Name],InStr([First Name]," ")-1),[Last Name])
and
IIf([Last Name] Like "* *",Left([Last Name],InStr([First Name]," ")+1),[Last Name])
Please advise.
Example:
De La Cruz
Cruz
Guzman Cruz
In all instances, I am hoping to receive
Cruz
I have tried
Right([Last Name],Len([Last Name])-InStr([Last Name],",")-1)
and
IIf([Last Name] Like "* *",Left([Last Name],InStr([First Name]," ")-1),[Last Name])
and
IIf([Last Name] Like "* *",Left([Last Name],InStr([First Name]," ")+1),[Last Name])
Please advise.
aikimark
Use InstrRev() to do your searching from the end of the string.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much.