CODEWITHSUNDEEP
sqlexcelvbadatems-access
DezyK
DezyK

Reputation: 65

SQL to compare date held in access to today's date not working

I'm trying to run a query, as part of VBA code, which looks at all the records containing today's date in a date field and writes them to a record set. The following SQL statement doesn't work however. It doesn't return any records, even though the DateEntered field definetely has records with today's date.

dim cn as new adodb.connection
dim rs as new adodb.recordset
dim SQL as string
SQL = "SELECT * FROM tbl1LinkAuths WHERE DateEntered = #" & Date & "# "
Debug.Print SQL
cn.Open cnDB
rs.Open SQL, cn, , adLockPessimistic

I use this form of database connection all the time and it works for other queries in my code, it's just the date recognition that's at fault.

Upvotes: 2

Views: 3691

Answers (1)

Gustav
Gustav

Reputation: 55806

You have several options:

SQL = "SELECT * FROM tbl1LinkAuths WHERE DateEntered = Date()"

or:

Dim DateToday As String
DateToDay = Format(Date, "yyyy\/mm\/dd")
SQL = "SELECT * FROM tbl1LinkAuths WHERE DateEntered = #" & DateToDay & "# "

Upvotes: 2

Related Questions