We help IT Professionals succeed at work.

Run-time error '3134' Syntax error in INSERT INTO statement

Hello experts,

I have been racking my brains trying to find where the error is and why the data will not insert into the Temporary Table. My code is attached. Any suggestions please?

Thanks
John
Private Sub Form_Dirty(Cancel As Integer)

'    Me.UserName = Forms!F_Startup![UserName]
    
    Dim strSQL As String
    Dim lngPtID As Long
    Dim strURN As String
    Dim strSurname As String
    Dim strGivenNames As String
    Dim varDOB As Variant
    Dim strSex As String
    Dim strAddLine1 As String
    Dim strAddLine2 As String
    Dim strSuburb As String
    Dim strPC As String
    Dim strIndigStatus As String
    Dim lngIndigStatusID As Long
    Dim strCountryOfBirth As String
    Dim lngCountryID As Long
    Dim strMedicareNo As String
    Dim strGuardSurname As String
    Dim strGuardGivenNames As String
    Dim strRelationship As String
    Dim strPhone As String
    Dim strWork As String
    Dim strMobile As String
    Dim strAltContact As String
    Dim strAltAdd1 As String
    Dim strAltAdd2 As String
    Dim strAltSuburb As String
    Dim strAltPC As String
    Dim strAltPhone As String
    Dim strAltMobile As String
    Dim strGP As String
    Dim strGPAdd As String
    Dim strGPAdd2 As String
    Dim strGPSuburb As String
    Dim strGPPC As String
    Dim strGPPhone As String
    Dim strGPFax As String
    Dim strGPEmail As String
    Dim varDOD As Variant

    lngPtID = IIf(IsNull(Me.Patient_ID), Null, Me.Patient_ID)                               'Number field
    strURN = IIf(IsNull(Me.UR_Number), "", Me.UR_Number)                                    'Text field
    strSurname = IIf(IsNull(Me.Surname), "", Me.Surname)                                    'Text field
    strGivenNames = IIf(IsNull(Me.Given_Names), "", Me.Given_Names)                         'Text field
    varDOB = IIf(IsNull(Me.DOB), Null, Me.DOB)                                              'Date/Time field
    strSex = IIf(IsNull(Me.Sex), "", Me.Sex)                                                'Text field
    strAddLine1 = IIf(IsNull(Me.Address_Line_1), "", Me.Address_Line_1)                     'Text field
    strAddLine2 = IIf(IsNull(Me.Address_Line_2), "", Me.Address_Line_2)                     'Text field
    strSuburb = IIf(IsNull(Me.Suburb), "", Me.Suburb)                                       'Text field
    strPC = IIf(IsNull(Me.Postcode), "", Me.Postcode)                                       'Text field
    strIndigStatus = IIf(IsNull(Me.Indigenous_Status), "", Me.Indigenous_Status)            'Text field
    lngIndigStatusID = IIf(IsNull(Me.Indigenous_Status_ID), 0, Me.Indigenous_Status_ID)     'Number field
    strCountryOfBirth = IIf(IsNull(Me.Country_of_Birth), "", Me.Country_of_Birth)           'Text field
    lngCountryID = IIf(IsNull(Me.CountryID), 0, Me.CountryID)                               'Number field
    strMedicareNo = IIf(IsNull(Me.MedicareNumber), "", Me.MedicareNumber)                   'Text field
    strGuardSurname = IIf(IsNull(Me.Guardian_Surname), "", Me.Guardian_Surname)             'Text field
    strGuardGivenNames = IIf(IsNull(Me.Guardian_Given_Names), "", Me.Guardian_Given_Names)  'Text field
    strRelationship = IIf(IsNull(Me.Relationship), "", Me.Relationship)                     'Text field
    strPhone = IIf(IsNull(Me.Phone_Home), "", Me.Phone_Home)                                'Text field
    strWork = IIf(IsNull(Me.Phone_Work), "", Me.Phone_Work)                                 'Text field
    strMobile = IIf(IsNull(Me.Phone_Mobile), "", Me.Phone_Mobile)                           'Text field
    strAltContact = IIf(IsNull(Me.Alternative_Contact), "", Me.Alternative_Contact)         'Text field
    strAltAdd1 = IIf(IsNull(Me.Alternative_Address_Line_1), "", Me.Alternative_Address_Line_1) 'Text field
    strAltAdd2 = IIf(IsNull(Me.Alternative_Address_Line_2), "", Me.Alternative_Address_Line_2) 'Text field
    strAltSuburb = IIf(IsNull(Me.Alternative_Suburb), "", Me.Alternative_Suburb)            'Text field
    strAltPC = IIf(IsNull(Me.Alternative_Postcode), "", Me.Alternative_Postcode)            'Text field
    strAltPhone = IIf(IsNull(Me.Alt_Contact_Phone), "", Me.Alt_Contact_Phone)               'Text field
    strAltMobile = IIf(IsNull(Me.Alt_Contact_Mobile), "", Me.Alt_Contact_Mobile)            'Text field
    strGP = IIf(IsNull(Me.GP_Name), "", Me.GP_Name)                                         'Text field
    strGPAdd = IIf(IsNull(Me.GP_Address), "", Me.GP_Address)                                'Text field
    strGPAdd2 = IIf(IsNull(Me.GP_Address_Line_2), "", Me.GP_Address_Line_2)                 'Text field
    strGPSuburb = IIf(IsNull(Me.GP_Suburb), "", Me.GP_Suburb)                               'Text field
    strGPPC = IIf(IsNull(Me.GP_Postcode), "", Me.GP_Postcode)                               'Text field
    strGPPhone = IIf(IsNull(Me.GP_Phone), "", Me.GP_Phone)                                  'Text field
    strGPFax = IIf(IsNull(Me.GP_Fax), "", Me.GP_Fax)                                        'Text field
    strGPEmail = IIf(IsNull(Me.GP_Email), "", Me.GP_Email)                                  'Text field
    varDOD = IIf(IsNull(Me.Date_of_Death), Null, Me.Date_of_Death)                          'Date/Time field
    
    strSQL = "INSERT INTO TEMPChangeDemographics ([Patient ID],[UR Number],[Surname],[Given Names],[DOB],[Sex],[Address Line 1],[Address Line 2])" & _
        "([Suburb],[Postcode],[Indigenous Status],[Indigenous Status ID],[Country Of Birth],[Country Of Birth ID],[Medicare Number])" & _
        "[Guardian Surname],[Guardian Given Names],[Relationship],[Phone Home],[Phone Work],[Phone Mobile],[Alternative Contact],[Alt Address Line 1])" & _
        "([Alt Address Line 2],[Alt Suburb],[Alt Postcode],[Alt Contact Phone],[Alt Contact Mobile],[GP Name],[GP Address],[GP Address Line 2])" & _
        "([GP Suburb],[GP Postcode],[GP Phone],[GP Fax],[GP Email],[Date of Death])" & _
        "VALUES(" & lngPtID & ",'" & strURN & " ','" & strSurname & " ','" & strGivenNames & " ',#" & Format(varDOB, "dd/mm/yyyy") & "#,'" & strSex & " ')" & _
        "('" & strAddLine1 & "','" & strAddLine2 & "','" & strSuburb & "','" & strPC & "','" & strIndigStatus & "'," & lngIndigStatusID & ")" & _
        "('" & strCountryOfBirth & "'," & lngCountryID & ",'" & strMedicareNo & "','" & strGuardSurname & "','" & strGuardGivenNames & "')" & _
        "('" & strRelationship & "','" & strPhone & "','" & strWork & "','" & strMobile & "','" & strAltContact & "','" & strAltAdd1 & "')" & _
        "('" & strAltAdd2 & "','" & strAltSuburb & "','" & strAltPC & "','" & strAltPhone & "','" & strAltMobile & "','" & strGP & "')" & _
        "('" & strGPAdd & "','" & strGPAdd2 & "','" & strGPSuburb & "','" & strGPPC & "','" & strGPPhone & "','" & strGPFax & "','" & strGPEmail & "')" & _
        "(#" & Format(varDOD, "dd/mm/yyyy") & "#)"

    CurrentDb.Execute strSQL, dbFailOnError
           
End Sub

Open in new window

Comment
Watch Question

I think you are missing a semi :

 "(#" & Format(varDOD, "dd/mm/yyyy") & "#)"

to:

 "(#" & Format(varDOD, "dd/mm/yyyy") & "#);"
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
I rather think you have too many (  ) around and not enough ,
typical copy/paste error:

strSQL = "INSERT INTO TEMPChangeDemographics ([Patient ID],[UR Number],[Surname],[Given Names],[DOB],[Sex],[Address Line 1],[Address Line 2]" & _
        ",[Suburb],[Postcode],[Indigenous Status],[Indigenous Status ID],[Country Of Birth],[Country Of Birth ID],[Medicare Number] " & _
        ",[Guardian Surname],[Guardian Given Names],[Relationship],[Phone Home],[Phone Work],[Phone Mobile],[Alternative Contact],[Alt Address Line 1] " & _
        ",[Alt Address Line 2],[Alt Suburb],[Alt Postcode],[Alt Contact Phone],[Alt Contact Mobile],[GP Name],[GP Address],[GP Address Line 2] " & _
        ",[GP Suburb],[GP Postcode],[GP Phone],[GP Fax],[GP Email],[Date of Death])" & _
        "VALUES(" & lngPtID & ",'" & strURN & " ','" & strSurname & " ','" & strGivenNames & " ',#" & Format(varDOB, "dd/mm/yyyy") & "#,'" & strSex & " ' " & _
        ",'" & strAddLine1 & "','" & strAddLine2 & "','" & strSuburb & "','" & strPC & "','" & strIndigStatus & "'," & lngIndigStatusID & " " & _
        ",'" & strCountryOfBirth & "'," & lngCountryID & ",'" & strMedicareNo & "','" & strGuardSurname & "','" & strGuardGivenNames & "' " & _
        ",'" & strRelationship & "','" & strPhone & "','" & strWork & "','" & strMobile & "','" & strAltContact & "','" & strAltAdd1 & "' " & _
        ",'" & strAltAdd2 & "','" & strAltSuburb & "','" & strAltPC & "','" & strAltPhone & "','" & strAltMobile & "','" & strGP & "' " & _
        ",'" & strGPAdd & "','" & strGPAdd2 & "','" & strGPSuburb & "','" & strGPPC & "','" & strGPPhone & "','" & strGPFax & "','" & strGPEmail & "' " & _
        ",#" & Format(varDOD, "dd/mm/yyyy") & "#)"

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
First, clean up the IIf lines:

    lngPtID = Nz(Me!Patient_ID, 0)
    strURN = Nz(Me!UR_Number)
    varDOB = Me!DOB

etc.
Then clean up your SQL and modify your date expressions:
 strSQL = "INSERT INTO TEMPChangeDemographics ([Patient ID],[UR Number],[Surname],[Given Names],[DOB],[Sex],[Address Line 1],[Address Line 2]," & _
        "[Suburb],[Postcode],[Indigenous Status],[Indigenous Status ID],[Country Of Birth],[Country Of Birth ID],[Medicare Number]," & _
        "[Guardian Surname],[Guardian Given Names],[Relationship],[Phone Home],[Phone Work],[Phone Mobile],[Alternative Contact],[Alt Address Line 1]," & _
        "[Alt Address Line 2],[Alt Suburb],[Alt Postcode],[Alt Contact Phone],[Alt Contact Mobile],[GP Name],[GP Address],[GP Address Line 2]," & _
        "[GP Suburb],[GP Postcode],[GP Phone],[GP Fax],[GP Email],[Date of Death]) " & _
        "VALUES(" & lngPtID & ",'" & strURN & "','" & strSurname & "','" & strGivenNames & "',#" & Format(varDOB, "mm\/dd\/yyyy") & "#,'" & strSex & "'," & _
        "'" & strAddLine1 & "','" & strAddLine2 & "','" & strSuburb & "','" & strPC & "','" & strIndigStatus & "'," & lngIndigStatusID & "," & _
        "'" & strCountryOfBirth & "'," & lngCountryID & ",'" & strMedicareNo & "','" & strGuardSurname & "','" & strGuardGivenNames & "'," & _
        "'" & strRelationship & "','" & strPhone & "','" & strWork & "','" & strMobile & "','" & strAltContact & "','" & strAltAdd1 & "'," & _
        "'" & strAltAdd2 & "','" & strAltSuburb & "','" & strAltPC & "','" & strAltPhone & "','" & strAltMobile & "','" & strGP & "'," & _
        "'" & strGPAdd & "','" & strGPAdd2 & "','" & strGPSuburb & "','" & strGPPC & "','" & strGPPhone & "','" & strGPFax & "','" & strGPEmail & "'," & _
        "#" & Format(varDOD, "mm\/dd\/yyyy") & "#)"

Open in new window

/gustav
Distinguished Expert 2017
Commented:
I'm not sure why you are replacing nulls with ZLS.  Having both in the table just leads to errors.  If you don't want to allow null, hen you should make the field required.  You can simplify the process by not creating the variables.  Instead, just use Me.yourfieldname & "" in the query.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Goog point. Perhaps all the string and long variables simply should be Variant - accepting Null or a value.

/gustav

Author

Commented:
Thanks guys,

Sorry I have not replied before but have been away from work until today. Have changed all the string and longs to Variant and works like a charm.

Cheers

John