Access VBA SQL String Syntax eror

Question

I am trying to delete all multiple records in ARPs and I can#t figure out why this doesn't work. I get an Error 3075 when I run this in ACCESS saying:

"Syntax Error in Date in Query 'ARPs.ARPNR = 'CLU7BG' AND ARPs.Änderungsdatum < #29.07.2016 12:21:42'"

Thanks for any help you can provide.

Dim strSQL As String
Dim allMult As DAO.Recordset

strSQL = "SELECT ARPs.ARPNR, ARPs.Änderungsdatum FROM ARPs WHERE ARPs.BTNR IN (SELECT BTNR FROM ARPs GROUP BY BTNR HAVING COUNT(*) > 1);"

Set allMult = CurrentDb.OpenRecordset(strSQL)

With allMult

    'Check that the Recordset isn't empty
    If Not .BOF And Not .EOF Then

        .MoveLast
        .MoveFirst

        'Loop until we reach the end of the Recordset
        While (Not allMult.EOF)

            'Delete all except the newest multiple records
            strSQL = "DELETE * FROM ARPs WHERE ARPs.ARPNR  = '" & allMult.Fields("ARPNR") & "' AND ARPs.Änderungsdatum < #" & allMult.Fields("Änderungsdatum") & "# ;"

            CurrentDb.Execute strSQL, dbFailOnError

            .MoveNext
        Wend

    End If

    'close the Recordset
    .Close
End With

'release memory
Set allMult = Nothing

Gustav · Accepted Answer · 2017-08-03 12:10:00Z

2

You need a properly formatted string expression for the date value:

strSQL = "DELETE * FROM ARPs WHERE ARPs.ARPNR = '" & allMult.Fields("ARPNR") & "' AND ARPs.Änderungsdatum < #" & Format(allMult.Fields("Änderungsdatum").Value, "yyyy\/mm\/dd") & "# ;"

answered Aug 3, 2017 at 12:10

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

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

3 Comments

Erik A Over a year ago

Don't forget the time (if required): Format(allMult.Fields("Änderungsdatum").Value, "yyyy\/mm\/dd hh:nn:ss")

Nicholas Menzel Over a year ago

It works! Thanks! I thought it would be fine, because both of the dates are in the same format due to being from the same table originally.

Gustav Over a year ago

Have in mind, that a date value is numeric and carries no format. The format applied in tables, queries, and forms is for display only.

Jonathan Willcock · Accepted Answer · 2017-08-03 12:40:55Z

0

Assuming your allMult.Fields("Änderungsdatum") is a Date then you can use QueryDef to allow you to pass parameters. In this case you don't need to worry about formatting.

With CurrentDb.CreateQueryDef("", _
  "DELETE FROM ARPs WHERE ARPNR = prm0 AND Änderungsdatum < prm1")
  .Parameters("prm0") = allMult.Fields("ARPNR") 
  .Parameters("prm1") = allMult.Fields("Änderungsdatum")
  .Execute
  .Close
End With

Using parameter queries is a good habit to get into. Including strings in a SQL string for execution is generally bad practice. Google SQL Injection to find out why.

answered Aug 3, 2017 at 12:40

Jonathan Willcock

5,4303 gold badges25 silver badges36 bronze badges

Collectives™ on Stack Overflow

Access VBA SQL String Syntax eror

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related