I have a table called tblAuditForms. This table includes a date column called AuditDate. The table stores the date in "short date" format, so the time is not stored in the table. I want to select records from this table where the date matches 6/11/2020. I've confirmed that there are many records within the table that contain this date.
Here's what I have so far:
Dim myAuditDate As Date
Dim db As DAO.Database
Dim rst As DAO.Recordset2
Dim strSQL As String
myAuditDate = CDate("6/11/2020")
set db = CurrentDb
strSQL = "SELECT * FROM tblAuditForms WHERE AuditDate = " & Format(myAuditDate, "mm/dd/yyyy") & ";"
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
The code makes it all the way down to the rst.MoveFirst line where it fails. It appears the recordset is empty. As I mentioned earlier, I know that the table contains records with that date that I'm providing. I suspect the problem is with the format of my SQL statement but I can't figure out how. I've also surrounded the date with # characters but that didn't appear to help. Including ' characters around the date fails at the time creating the recordset as well.
I appreciate any help. Thanks!