extracting partial field value

[trainer4063]

using access 2000 database. I have a need to extract a partial value in a field. for example I have a table with full names, for example "John Smith", "Jamie Johnson", and I need to be able to extract just the last name. Basically everything right of the last space.

Help?

[missinglinq]

You have to understand that this will only work if the names are always entered in the format you gave, i.e. "Jamie Johnson."

[CODE=vb] LastName = right(FullName, (len(FullName)-instr(FullName, " ")))[/CODE]

Good Luck and Welcome to TheScripts!

Linq ;0)>

[trainer4063]

Thanks, worked great. but like you said, it only works if there is only one space in the field. If the field has a entry like "Jamie V Johnson", "V Johnson" shows. Is there a way to pull just "Johnson"?

[MMcCarthy]

Try this ...

[CODE=vb]
Dim pos As Integer
Dim newpos As Integer
Dim lastname As String
Dim rpt As Boolean

pos = 1
rpt = True

Do Until rpt = False
newpos = InStr(pos + 1, FullName, " ")
If newpos <> 0 Then
pos = newpos
Else
rpt = False
End If
Loop

lastname = Right(FullName, Len(FullName) - pos)
[/CODE]