1

I am trying to do something in Access that is truly simple but I can't seem to get it to work. I have researched the answer online and tried many different things that were suggested to get this code to run in VBA but to no avail.

The value in Me.txtImportStartGeneralDate is '7/11/2018 2:27:19 PM' When I create a delete query using Access query design, it deletes the records perfectly fine. However the SQL statement in the VBA code below throws a Runtime error 3464.

I've tried both with and without the hash marks surrounding the General Date format. I've tried to cast the date/time using CDate(). I've tried replacing the variable as defined below with, " & Me.txtImportStartGeneralDate & " , nothing seems to work.

Private Sub cmdUndoImport_Click()

    MsgBox "Are you sure you want to do this?", vbOKCancel, "THIS CANNOT BE UNDONE!!!"

    strLastImport = Me.txtImportStartGeneralDate
    strLastImportStamp = "#" & strLastImport & "#"
    DoCmd.SetWarnings False
    SQL1 = ("DELETE dbo_Subjects.*, dbo_Subjects.Date_Created FROM dbo_Subjects " & _
                "WHERE (((dbo_Subjects.Date_Created)>='" & strLastImportStamp & "'));")

    DoCmd.RunSQL SQL1
    SQL2 = ("DELETE dbo_Specimens.*, dbo_Specimens.Date_Created FROM dbo_Specimens " & _
                "WHERE (((dbo_Specimens.Date_Created)>='" & strLastImportStamp & "'));")
    DoCmd.RunSQL SQL2
    SQL3 = ("DELETE dbo_Samples.*, dbo_Samples.Date_Created FROM dbo_Samples " & _
                "WHERE (((dbo_Samples.Date_Created)>='" & strLastImportStamp & "'));")
    DoCmd.RunSQL SQL3
End Sub
dbmitch
  • 5,361
  • 4
  • 24
  • 38
ReciprocalHokie
  • 19
  • 2
  • Maybe remove the apostrophe delimiters. – June7 Jul 11 '18 at 19:31
  • Your tabled names indicate some kind of linked SQL Server connection yet you don't mention that in your question or in tags. Do you have linked tables? Are you trying to use sql pass through queries? – dbmitch Jul 11 '18 at 22:01

1 Answers1

3

In Access SQL dates need to be surrounded by octothorpes, and formatted either mm/dd/yyyy or yyyy-mm-dd. The main error here is the quotes instead of octothorpes.

SQL1 = ("DELETE dbo_Subjects.*, dbo_Subjects.Date_Created FROM dbo_Subjects " & _
                "WHERE (((dbo_Subjects.Date_Created)>=#" & strLastImportStamp & "#));")

Alternatively, you can use parameters or TempVars, but that does require it to be an actual date.

Erik A
  • 31,639
  • 12
  • 42
  • 67