How to split a Name Field that has incorrect data?

Question

I have a table with a field called PATRN_NAME which is set up with First_Name, Last_Name M.I.

Examples:

Smith, James M

Jones, Chris J.

I am trying to break up the field into FIRST_NAME, LAST_NAME and MI fields. I just asked a question about this and someone helped me use Split() to get the LAST_NAME field. However, when I try to use the Split() function for the FIRST_NAME it does not work because the field has records that do not follow the name convention of the field and instead are as follows: "Town Library - GW" or "Donation from New York City".

When my code encounters these types of names it throws the error "Subscript out of range" on the line where I am using rst!FIRST_NAME = Split(Trim(Split(rst!PATRN_NAME, ",")(1)), " ")(0). How can I make my code run only on the data that follows the standard name convention for most of the field?

Function Change_Name()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Active Patrons", dbOpenDynaset)

rst.MoveFirst

Do While Not rst.EOF

    rst.Edit
    rst!LAST_NAME = Split(rst!PATRN_NAME, ",")(0)
    rst!FIRST_NAME = Split(Trim(Split(rst!PATRN_NAME, ",")(1)), " ")(0)
    rst.Update
    rst.MoveNext

Loop
End Function

Answer 1

You have two splits: once for comma; another for space. So declare two string arrays to hold the results from those splits.

Dim astrComma() As String
Dim astrSpace() As String

Then I think it will be simpler using those arrays in your loop.

rst.Edit
astrComma = Split(rst!PATRN_NAME, ",")
If UBound(astrComma) > 0 Then
    ' this means PATRN_NAME contains at least one comma,
    ' so assume LAST_NAME is everything before first comma
    rst!LAST_NAME = astrComma(0)
    ' expect FIRST_NAME present in second member of astrComma
    astrSpace = Split(Trim(astrComma(1)), " ")
Else
    MsgBox "no LAST_NAME in " & rst!PATRN_NAME
End If

If UBound(astrSpace) >= 0 Then
    ' you may also want to check whether this is an empty
    ' string before you store it; does the field allow 
    ' empty strings?
    rst!FIRST_NAME = astrSpace(0)
Else
    MsgBox "no FIRST_NAME  in " & rst!PATRN_NAME
End If
rst.Update

Answer 2

IF no comma in field then Last_name will equal Patrn_name so

rst!last = split()....

if rst!lasst <> rst!patrn_name) then rst!first = split()....