Access 2010 VBA Syntax Error '3075'

Question

I'm not sure if i missed something, I'm trying to find wheres that syntax error but i simply can't, maybe my eyes are bleeding because i spent like 2 hours trying to get whats wrong.

form code:

Option Compare Database

    Private Sub btnZatwierdzAccessories_Click()

        Dim strSQLdeleteAccessories As String

        Dim strSQLzm1accessoriesA As String
        Dim strSQLzm1accessoriesB As String
        Dim strSQLzm1accessoriesC As String
        Dim strSQLzm1accessoriesD As String

        Dim strSQLzm2accessoriesA As String
        Dim strSQLzm2accessoriesB As String
        Dim strSQLzm2accessoriesC As String
        Dim strSQLzm2accessoriesD As String

        Dim strSQLzm3accessoriesA As String
        Dim strSQLzm3accessoriesB As String
        Dim strSQLzm3accessoriesC As String
        Dim strSQLzm3accessoriesD As String

            strSQLdeleteAccessories = "DELETE * FROM [dbGrafikAccessories] WHERE dataAccessories = #" & Format(Me!txtDataAccessories, "d-m-yyyy") & "#;"

            strSQLzm1accessoriesA = "INSERT INTO dbGrafikAccessories (imieNazwisko, numerTelefonu, zmiana, praca, zaklad, godzina, dataAccessories, dataAccessoriesDo) VALUES ('" & Me!listZM1accessoriesA.Column(0) & "', '" & Me!listZM1accessoriesA.Column(1) & "', 'zm1', 'automatyk', 'accessories', '06001400',  #" & Format(Me!txtDataAccessories, "d-m-yyyy") & "#, #" & Format(Me!txtDataAccessoriesDo, "d-m-yyyy") & "#);"
            strSQLzm1accessoriesB = "INSERT INTO dbGrafikAccessories (imieNazwisko, numerTelefonu, zmiana, praca, zaklad, godzina, dataAccessories, dataAccessoriesDo) VALUES ('" & Me!listZM1accessoriesB.Column(0) & "', '" & Me!listZM1accessoriesB.Column(1) & "', 'zm1', 'piankowanie', 'accessories', '06001400',  #" & Format(Me!txtDataAccessories, "d-m-yyyy") & "#, #" & Format(Me!txtDataAccessoriesDo, "d-m-yyyy") & "#);"
            strSQLzm1accessoriesC = "INSERT INTO dbGrafikAccessories (imieNazwisko, numerTelefonu, zmiana, praca, zaklad, godzina, dataAccessories, dataAccessoriesDo) VALUES ('" & Me!listZM1accessoriesC.Column(0) & "', '" & Me!listZM1accessoriesC.Column(1) & "', 'zm1', 'szycie', 'accessories', '06001400',  #" & Format(Me!txtDataAccessories, "d-m-yyyy") & "#, #" & Format(Me!txtDataAccessoriesDo, "d-m-yyyy") & "#);"
            strSQLzm1accessoriesD = "INSERT INTO dbGrafikAccessories (imieNazwisko, numerTelefonu, zmiana, praca, zaklad, godzina, dataAccessories, dataAccessoriesDo) VALUES ('" & Me!listZM1accessoriesD.Column(0) & "', '" & Me!listZM1accessoriesD.Column(1) & "', 'zm1', 'dodatkowy', 'accessories', '06001400',  #" & Format(Me!txtDataAccessories, "d-m-yyyy") & "#, #" & Format(Me!txtDataAccessoriesDo, "d-m-yyyy") & "#);"

            strSQLzm2accessoriesA = "INSERT INTO dbGrafikAccessories (imieNazwisko, numerTelefonu, zmiana, praca, zaklad, godzina, dataAccessories, dataAccessoriesDo) VALUES ('" & Me!listZM2accessoriesA.Column(0) & "', '" & Me!listZM2accessoriesA.Column(1) & "', 'zm2', 'automatyk', 'accessories', '14002200',  #" & Format(Me!txtDataAccessories, "d-m-yyyy") & "#, #" & Format(Me!txtDataAccessoriesDo, "d-m-yyyy") & "#);"
            strSQLzm2accessoriesB = "INSERT INTO dbGrafikAccessories (imieNazwisko, numerTelefonu, zmiana, praca, zaklad, godzina, dataAccessories, dataAccessoriesDo) VALUES ('" & Me!listZM2accessoriesB.Column(0) & "', '" & Me!listZM2accessoriesB.Column(1) & "', 'zm2', 'piankowanie', 'accessories', '14002200',  #" & Format(Me!txtDataAccessories, "d-m-yyyy") & "#, #" & Format(Me!txtDataAccessoriesDo, "d-m-yyyy") & "#);"
            strSQLzm2accessoriesC = "INSERT INTO dbGrafikAccessories (imieNazwisko, numerTelefonu, zmiana, praca, zaklad, godzina, dataAccessories, dataAccessoriesDo) VALUES ('" & Me!listZM2accessoriesC.Column(0) & "', '" & Me!listZM2accessoriesC.Column(1) & "', 'zm2', 'szycie', 'accessories', '14002200',  #" & Format(Me!txtDataAccessories, "d-m-yyyy") & "#, #" & Format(Me!txtDataAccessoriesDo, "d-m-yyyy") & "#);"
            strSQLzm2accessoriesD = "INSERT INTO dbGrafikAccessories (imieNazwisko, numerTelefonu, zmiana, praca, zaklad, godzina, dataAccessories, dataAccessoriesDo) VALUES ('" & Me!listZM2accessoriesD.Column(0) & "', '" & Me!listZM2accessoriesD.Column(1) & "', 'zm2', 'dodatkowy', 'accessories', '14002200',  #" & Format(Me!txtDataAccessories, "d-m-yyyy") & "#, #" & Format(Me!txtDataAccessoriesDo, "d-m-yyyy") & "#);"

            strSQLzm3accessoriesA = "INSERT INTO dbGrafikAccessories (imieNazwisko, numerTelefonu, zmiana, praca, zaklad, godzina, dataAccessories, dataAccessoriesDo) VALUES ('" & Me!listZM3accessoriesA.Column(0) & "', '" & Me!listZM3accessoriesA.Column(1) & "', 'zm3', 'automatyk', 'accessories', '22000600',  #" & Format(Me!txtDataAccessories, "d-m-yyyy") & "#, #" & Format(Me!txtDataAccessoriesDo, "d-m-yyyy") & "#);"
            strSQLzm3accessoriesB = "INSERT INTO dbGrafikAccessories (imieNazwisko, numerTelefonu, zmiana, praca, zaklad, godzina, dataAccessories, dataAccessoriesDo) VALUES ('" & Me!listZM3accessoriesB.Column(0) & "', '" & Me!listZM3accessoriesB.Column(1) & "', 'zm3', 'piankowanie', 'accessories', '22000600',  #" & Format(Me!txtDataAccessories, "d-m-yyyy") & "#, #" & Format(Me!txtDataAccessoriesDo, "d-m-yyyy") & "#);"
            strSQLzm3accessoriesC = "INSERT INTO dbGrafikAccessories (imieNazwisko, numerTelefonu, zmiana, praca, zaklad, godzina, dataAccessories, dataAccessoriesDo) VALUES ('" & Me!listZM3accessoriesC.Column(0) & "', '" & Me!listZM3accessoriesC.Column(1) & "', 'zm3', 'szycie', 'accessories', '22000600',  #" & Format(Me!txtDataAccessories, "d-m-yyyy") & "#, #" & Format(Me!txtDataAccessoriesDo, "d-m-yyyy") & "#);"
            strSQLzm3accessoriesD = "INSERT INTO dbGrafikAccessories (imieNazwisko, numerTelefonu, zmiana, praca, zaklad, godzina, dataAccessories, dataAccessoriesDo) VALUES ('" & Me!listZM3accessoriesD.Column(0) & "', '" & Me!listZM3accessoriesD.Column(1) & "', 'zm3', 'dodatkowy', 'accessories', '22000600',  #" & Format(Me!txtDataAccessories, "d-m-yyyy") & "#, #" & Format(Me!txtDataAccessoriesDo, "d-m-yyyy") & "#);"

            DoCmd.SetWarnings False

                DoCmd.RunSQL strSQLdeleteAccessories

                DoCmd.RunSQL strSQLzm1accessoriesA
                DoCmd.RunSQL strSQLzm1accessoriesB
                DoCmd.RunSQL strSQLzm1accessoriesC
                DoCmd.RunSQL strSQLzm1accessoriesD

                DoCmd.RunSQL strSQLzm2accessoriesA
                DoCmd.RunSQL strSQLzm2accessoriesB
                DoCmd.RunSQL strSQLzm2accessoriesC
                DoCmd.RunSQL strSQLzm2accessoriesD

                DoCmd.RunSQL strSQLzm3accessoriesA
                DoCmd.RunSQL strSQLzm3accessoriesB
                DoCmd.RunSQL strSQLzm3accessoriesC
                DoCmd.RunSQL strSQLzm3accessoriesD

            DoCmd.SetWarnings True

    End Sub

debug redirects me to the line:

    DoCmd.RunSQL strSQLzm1accessoriesA

With error message:

Run-time error '3075':
Syntax error in date in query expression '#'.

Gustav · Accepted Answer · 2016-04-08 14:59:15Z

1

First, your date format should read like:

.. #" & Format(Me!txtDataAccessories, "yyyy\/mm\/dd") & "#,

Second, if the textbox in empty, the SQL will fail.

So do write out the created SQL to debug:

Debug.Print strSQLzm1accessoriesA

Then study the output.

answered Apr 8, 2016 at 14:59

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Eska Over a year ago

I tried the solution, like "dd\/nn\/yyyy" but I'm getting same error over and over. Adding Debug.Print strSQLzm1accessoriesA doesn't return anything.

Eska Over a year ago

Ok, well, thats awkward, i had duplicated date field that wasnt populated which leaded me to this issue. I really appricetate Your interest in this matter. Thank You!

Gustav Over a year ago

Right, dd\/nn\/yyyy would not give much as nn returns minutes.

Eska Over a year ago

oh, sorry, thats a typo, it was mm there, in comment i misstyped it :)

Collectives™ on Stack Overflow

Access 2010 VBA Syntax Error '3075'

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related