How to parse a field in a Select Query

[ausmoran]

I've done it many times before, but I cannot recall how to parse the LASTNAME out of a field that contains LastName FirstName MiddleInit. I seem to recall that I used the LTRIM function....but I can't get it to work. I want to create a LASTNAME field so need to extract ONLY the lastname from the LASTFIRSTMIDDLE field.

Any assistance will be appreciated greatly!

Austin

 

[Rich_Lovina]

Many ways to do this, depending on yr data layout & exceptions. Try

Original Entry in [Names]: = "John Doe"
Returned by Expression: Doe
Expression:Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(1, _
[Names]," "))
OR TRY
Original Entry in [Names]: "Doe, John"
Returned by Expression: Doe
Expression: Expr: Left([Names],InStr(1,[Names],",")-1)
OR TRY
Original Entry in [Names]: "John P. Doe"
Returned by Expression: Doe
Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(InStr _
(1, [Names]," ")+1,[Names]," "))
OR TRY
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]," ")))

AND FAILING THAT
You can use VB Code to split the lot, but try these first.