Access - Name Separation - with/out middle

names are a PITA.

assuming you have:

strName = "JOHN WILLIAMS DOE"

YOu could use:
FirstName = Split(strName, " ")(0)
LastName = Split(strName, " ")(2)

But if you have:

strName = "JOHN DOE"

Then lastname won't work.  You could put that into a custom function, but even then, just taking the last segment as the last name might not work for names like:

John William Van Buren

But if you built your own function, you could include some logic, something like:

Public Function fnNamePart(FullName as string, Part as string) as string

    Dim arName() as String
    dim intParts as string

    arName = SPLIT(FullName, " ")
    
    SELECT CASE Part 
        Case "First"
            fnNamePart = arName(0)
        Case "Middle"
            if Ubound(arName) > 1 then
                fnNamePart = arName(1)
            endif
        Case "Last"
            if Ubound(arName) >= 3 Then
                fnNamePart = arName(2) & " " & arName(3)
            else
                fnNamePart = arName(ubound(arName))
            endif
    End Select

End Function

Select allOpen in new window

Dale,
I think you shouldn't ever assume that the last name is in slot 3.  I would assume it was always in the ubound slot and assume the first name is in slot1.  That works as long as there are no prefixes/suffixes and no multi-part first/last names.

I have tried this without success

FirstName:Split (([Patient First/Last], " ")(0))

Also tried this without success:

LName: Left([Patient First/Last],InStr([Name]," "))

Thank you.

@Pat,

The point of testing for the UBound(arName) > 3 was to determine whether there were 4 or more parts to the name.  If so, then my assumption is that the last two are a multi-part last name.  This could, of course be a bad assumption, as some have two middle names (William Jim Bob Redneck), although in this case, Jim-Bob might be hyphenated.  

If it isn't a 4 part name, then the assumption is First, Middle, Last, although this could be incorrect as well (First, two part Last).  This is why I hate working with single field names!

@exp vg
You cannot use the Split function directly in a query.

What does "without success" mean?
Show us the input data.  Show us the output result.

You can only get the First Name with those two simple expressions.  You need to implement Dale's suggested code to get the last Name.  Or if you want something a little simpler that only takes one call:

Public Function FLName(FullName as string) as string

    Dim arName() as String
    Dim FIrstName as variant
    Dim LastName as Variant

              arName = SPLIT(FullName, " ")
    
              FirstName = arName(0)
              LastName = arName(ubound(arName))

             FLName = FirstName & " " & Lastname

End Function
                           

Select allOpen in new window

If you want to try using left, it would look like:

FName: Trim(Left([Patient First/Last],InStr([Patient First/Last]," ")))
LName: Trim(Right([Patient First/Last], instrrev([Patient First/Last], " ")))

but like Pat and I said, this will not work for multi-part last names.

Thank you Dale - the first name worked, but the last name truncated or added extra characters - even when the name was not more than two parts

Please advise.

Try the function I wrote.

When working with public functions it is best to store them in standard modules so you can use them from anywhere.  So, you can use the function in code, in an expression, or in a query.

Sorry,  That should have been:

LName: Trim(Mid([Patient First/Last], instrrev([Patient First/Last], " ")))

Using Mid rather than Right.  The InstrRev( ) function will give you the position number (from the left) of the first occurance of a character, working backwards from the right.  

InstrRev("John Doe", " ") would return 5.  And then the Right("John Doe", 5) would return "n Doe".  But the Mid( ) function starts as the character position provided and works to the right.

Thank you Dale so much - is there a way for me to reward you points as well even though this question was closed. You have been offering a lot of help.

don't worry about it.  just wanted to provide you with some additional options.