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."

 LastName = right(FullName,(len(FullName)-instr(FullName," ")))

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 ...

 
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)