Parsing Name Field Issues 1

[gammyb]

I have researched and tried to back into a solution to no avail. Previously I had help here learning how to unconcatenate last and first name when name is all in one field, example "Smith Sr,John A"

The last name parse works well, it even keeps the suffix if they are present (e.g. "Smith Sr"):
LastName: Left([name],InStr([name],",")-1)

The first name parse works OK, but it keeps the middle initial or name if it is present (e.g. "John A":
FirstName: Mid([name],InStr([name],",")+1)

I want to parse the first name so that it excludes middle initial or middle name if present, and I need a new parse that will parse out middle initial/name if they exist, but if they don't, leave the field blank.

I have spent hours on this, nothing returns quite the exact result that I need. Sure hope someone can help.

 

[MajP]

You code put these in a standard module and use them in a query:

Public Function getFirst(varName As Variant) As String
  If Not IsNull(varName) Then
    getFirst = Trim(getFirstAndMiddle(varName))
    getFirst = Trim(Split(getFirst, " ")(0))
  End If
End Function

Public Function getLast(varName As Variant) As String
  If Not IsNull(varName) Then
    getLast = Trim(Split(varName, ",")(0))
  End If
End Function

Public Function getFirstAndMiddle(varName As Variant) As String
  If Not IsNull(varName) Then
     getFirstAndMiddle = Trim(Split(varName, ",")(1))
  End If
End Function

Public Function getMiddle(varName As Variant)
    On Error GoTo errLbl
    getMiddle = Trim(getFirstAndMiddle(varName))
    getMiddle = Trim(Split(getMiddle, " ")(1))
    Exit Function
errLbl:
    If Err.Number = 9 Then
      Exit Function
    Else
      MsgBox Err.Number & Err.Description
    End If
End Function

to test

Public Sub Test()
  Dim str As String
  str = "Smith Sr,John A"
  Debug.Print getLast(str)
  Debug.Print getFirst(str)
  Debug.Print getFirstAndMiddle(str)
  Debug.Print getMiddle(str)
  str = "Smith, John"
  Debug.Print getLast(str)
  Debug.Print getFirst(str)
  Debug.Print getFirstAndMiddle(str)
  Debug.Print getMiddle(str)
End Sub

Example in a query
LastName:getLast([name])