0

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!

1 Answer 1

0

Upon further examination of my table I discovered that the AuditDate field was in fact storing the time as well. This was because those particular records had been inserted into the table using a separate VBA macro and were not subject to any validation rules.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .