CODEWITHSUNDEEP
sqlms-access
Adarsh Madrecha
Adarsh Madrecha

Reputation: 7956

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.

Upvotes: 0

Views: 2216

Answers (2)

marlan
marlan

Reputation: 1485

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])

Upvotes: 2

D Stanley
D Stanley

Reputation: 152644

Don't worry about formatting the date, just use a date comparison:

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

Upvotes: 1

Related Questions