CODEWITHSUNDEEP
sqlms-accessvba
ReciprocalHokie
ReciprocalHokie

Reputation: 19

How do I code a date variable into an Access sql statement

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

Upvotes: 1

Views: 2817

Answers (1)

Erik A
Erik A

Reputation: 32682

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.

Upvotes: 3

Related Questions