Adarsh Adarsh - 5 months ago 30
SQL Question

Date - Data type mismatch in criteria expression in Access

I have a table which stores birthdate in the field

DateValueOf
. I am creating a query where user can input any date and the query will return customers which have birthday on that day.

For this, I am trying to convert all the year in
DateValueOf
to current year.

eg > #13-10-1996# to #13-10-2016#
eg > #13-10-2001# to #13-10-2016#


So that I will then ask user to select date from date picker and run the below query.

SELECT CustomerID
WHERE FormatDateTime(DateValue(DateSerial(Year(Date()),Month([DateValueOf]),Day([DateValueOf]))),2) >= #13-10-2016#



But this SQL statement produces an error
data type mismatch in criteria expression



Please highlight my mistake. Thanks in advance. I am also open to any other way to get who's birthday it is today.

Answer

Assuming you receive the date from a date picker Control (call it: myDatePicker), today's date, returned by function Date() is not relevant.
And unless you want the age, Year() is not relevant either.

SELECT CustomerID 
FROM MyTableName
WHERE Month(myDatePicker) = Month([DateValueOf]) AND Day([DateValueOf]) = Day([myDatePicker])

If you want the customer's age at this birthday, you can add the calculation to the SELECT clause:

SELECT CustomerID, Year(myDatePicker) - Year(DateValueOf) As CustomerAge 
FROM MyTableName
WHERE Month(myDatePicker) = Month([DateValueOf]) AND Day([DateValueOf]) Day([myDatePicker])