Why am I getting a Data Type Mismatch when comparing a date in Microsoft Access even though I am using the # delimiter?

I have a table of people’s names who have attended a class and the timestamp of when they attended that class. This is stored in Access. I am trying to get the names of people who attended class on a given day. The code I wrote is as follows:

SELECT [Person Name], DateValue([Activity Start Date]) FROM [2019 Learning]
WHERE DateValue([Activity Start Date]) = #11/14/2019#

Even though the date I am looking for is contained in the table, when I run the query it works for a second and then I get an error message saying

“Data type mismatch in criteria expression.”

It then changes all the data returned into “#Name?“. Can someone please help explain why I am getting this error?

Answer

DateValue() function errors on null. Provide an alternate value. Can use Nz() function.

DateValue(Nz([Activity Start Date], Date())) = #11/14/2019#

Or use a date assumed would not be in the database, such as #12/31/2999#, instead of Date().

If you want to make double sure records with null are not captured, include additional filter criteria but still deal with Null in the equality. And with dynamic input like:

DateValue(Nz([Activity Start Date], Date())) = [Forms]![MyForm]![MyControl] AND NOT [Activity Start Date] IS NULL