Can't find a SQL syntax error

[GraeagleBill]

Both arrays FT and NameArr are arrays DIM'd global to the current general module. They are loaded using the "Split" function elsewhere in the module. There's no problem with any of the elements. However, more often than not the FT array elements 5, 6 and 7 are zero length strings. I keep thinking that's why sql gets all excited. See the setting of strsql towards the bottom of the posted code. (I've set FT(5) and Ft(7) to "None", but that didn't seem to matter.)

Before spending any time with this, here's one of the logged errors:

"SQL execution error: 3134 Syntax error in INSERT INTO statement."
"Family Name = Ackerman,Ron"
"SQL STMT: INSERT INTO Families (FamilyName,FamilyAddress,FamilyCityState,FamilyZi p,FamilySalutation,FamilyPhone,FamilyE-Mail) Values(""Ackerman,Ron"",""645 J Street"",""GERING, NE"",""69341-3377"",""1"",""none"",""none"")"

Notice the comma in the FamilyName "Ackerman,Ron". Could it be that's the problem? If so, any syntax suggestions to get around that?

Private Sub ImportFamily()
Dim strFamilyName As String
Dim strFamilyCtySt As String
Dim strSpouseName As String
Dim strSalutation As String
Dim strsql As String
Dim I As Integer
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=**=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' FT array elements:  Name(0),Addr(1),City(2),State(3),Zip(4),Phone1(5),Phone2(6),Email(7)
' NameArr array elements:  LastName(0) FirstName(1) conjunction(2) FirstName(3)
' We should be fairly "scrubbed" by the time we get here.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=**=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*

On Error GoTo FamilyAddErr

    strFamilyName = NameArr(0) & "," & NameArr(1)
    
    If UBound(NameArr) = 3 Then
        strSpouseName = NameArr(3)
        strSalutation = "2"
    Else
        strSpouseName = ""
        strSalutation = "1"
    End If
    
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Make sure family doesn't already exist.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    If Not IsNull(DLookup("FamilyName", "Families", "[FamilyName] = """ & strFamilyName & """")) Then Exit Sub
  
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Okay, add the family to the Families table
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*

strFamilyCtySt = FT(2) & ", " & FT(3)

strsql = "INSERT INTO Families (FamilyName,FamilyAddress,FamilyCityState,FamilyZip,FamilySalutation,FamilyPhone,FamilyE-Mail)"
strsql = strsql & " Values(""" & strFamilyName & """,""" & FT(1) & """,""" & strFamilyCtySt & ""","
strsql = strsql & """" & FT(4) & """,""" & strSalutation & """,""" & FT(5) & """,""" & FT(7) & """)"


CurrentDb.Execute strsql, dbFailOnError

Exit Sub

FamilyAddErr:
    Write #1, "SQL execution error: " & Err.Number & " " & Err.Description
    Write #1, "Family Name = " & strFamilyName
    Write #1, "SQL STMT: " & strsql
    Write #1, ""
    Exit Sub

End Sub

[pbaldy]

Hey Bill! First thing I notice is the inadvisable symbol in the email field name, requiring it to be bracketed. Fix that and see where we are.

[GraeagleBill]

Okay, that fixed it. This app is about 12-years old. It's amazing I haven't bumped my head on this issue long before this.
Thanks,
Bill
(PS) Ought to be a bit toasty over your way. About 92 here.

[pbaldy]

Happy to help Bill! Pretty toasty, but the wife was in Vegas working for a week and it was really hot there.