"Data Type Mismatch in criteria expression" in MS-Access SQL

Question

The MS-Access SQL Query below returns a "Data Type Mismatch in criteria expression" error

SELECT DateSerial(2016,Month(DOB),Day(DOB)) AS AnnDate
FROM DonorMaster WHERE  DateSerial(2016,Month(DOB),Day(DOB)) > Date()

When run without the ">" operator it runs correctly and all the returned rows have data.

What should be done to fix the error?

Answer 1

Your table most likely consists null values for one or more DOB's. This will throw the error. Redesign your query, for instance like this:

SELECT DateSerial(2016,Month(Nz(DOB, Date())),Day(Nz(DOB, Date()))) AS AnnDate
FROM DonorMaster 
WHERE DateSerial(2016,Month(Nz(DOB, Date())),Day(Nz(DOB, Date()))) >Date();