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

JOHN_STIBBARDAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leo AlexanderCommented:
I think you are missing a semi :

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

to:

 "(#" & Format(varDOD, "dd/mm/yyyy") & "#);"
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Gustav BrockCIOCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PatHartmanCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Goog point. Perhaps all the string and long variables simply should be Variant - accepting Null or a value.

/gustav
JOHN_STIBBARDAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.