Access Query: Last_Name, First_Name, Middle Initial Sometimes

Perhaps the following functions will help:

Public Function FirstName(varFullName)

    If Not IsNull(varFullName) Then

        On Error Resume Next

        FirstName = Left$(varFullName, InStr(varFullName, " ") - 1)

    End If

End Function

Public Function MiddleName(varFullName)

    Dim intFirstSpace As Integer, intSecondSpace As Integer

    If Not IsNull(varFullName) Then

        intFirstSpace = InStr(1, varFullName, " ")

        If intFirstSpace > 0 Then

            intSecondSpace = InStr(intFirstSpace + 1, varFullName, " ")

            If intSecondSpace > 0 Then

                MiddleName = Mid$(varFullName, intFirstSpace + 1, intSecondSpace - intFirstSpace)

            End If

        End If

    End If

End Function

Public Function Lastname(varFullName)

    Dim intLength As Integer, intCount As Integer

    If Not IsNull(varFullName) Then

        intLength = Len(varFullName)

        For intCount = intLength To 1 Step -1

            If Mid(varFullName, intCount, 1) = " " Then

                Lastname = Mid(varFullName, intCount + 1)

                Exit For

            End If

        Next intCount

    End If

End Function

Beware of names like Victoria de los Angeles, however.

Ken Sheridan, Stafford, England

Perhaps I confused you with breaking down the process to get the first name.   

You should only use three expressions in your query.

LastName

     Left(FullName,Instr(1,FullName & ",",",")-1)

MiddleIntial

     IIF(Right(FullName,2) Like " *",Right(Fullname,1),Null)

FirstName

    TRIM( LEFT(Mid(FullName,Instr(1,FullName,", ")+1) ,Instr(Mid(FullName,Instr(1,FullName,", ")+1) & " "," ")-1))

John Spencer Access MVP 2002-2005, 2007-2011 The Hilltop Institute University of Maryland Baltimore County

Come to think of it those functions were written so long ago that I think they probably pre-date the InstrRev function, so the last can more efficiently be rewritten as:

Public Function Lastname(varFullName)

    If Not IsNull(varFullName) Then

        On Error Resume Next

        Lastname = Mid$(varFullName, InStrRev(varFullName, " ") + 1)

    End If

End Function

Ken Sheridan, Stafford, England

As an aside here this points out why fields should always be the smallest piece of data. its a lot easier to concatenate feields togather then to break them apart. As John noted, while the formulas given will catch most instances, it won't catch names like  Anthony, Mary Anne B". I'm assuming you got the data this way so had no choice.

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

If I understand you correctly, you have a field with Last Name a comma and (optionally) a space and then first name and possibly a space plus the middle initial.  If this

You want to split that into its component parts.

LastName

     Left(FullName,Instr(1,FullName & ",",",")-1)

MiddleIntial

     IIF(Right(FullName,2) Like " *",Right(Fullname,1),Null)

FirstName is the tricky part

    Mid(FullName,Instr(1,FullName,", ")+1)  (call this X)

Then the expression to get FirstName from X is

    TRIM( LEFT(X,Instr(X & " "," ")-1))

So the expression for the first name and trim off the middle intial is

    TRIM( LEFT(Mid(FullName,Instr(1,FullName,", ")+1) ,Instr(Mid(FullName,Instr(1,FullName,", ")+1) & " "," ")-1))

This won't handle names like  "Anthony, Mary Anne B"  but it should handle "de la Hoya, Oscar L"

John Spencer Access MVP 2002-2005, 2007-2011 The Hilltop Institute University of Maryland Baltimore County