Data Type Mismatch

[Mtyetti2]

I am using a query to calculate the amount of days between today and a Date field in my table. [Birthday]
The query returns a value, but as soon as I try to apply a criteria to that value, I get "Data Type Mismatch".
The Birthday is stored as a date. Here is the expression:
Expr1: (Date() - [Birthday])


I am trying to apply a criteria like "<60" to return personnel records who have birthdays within 60 days.

Any help is greatly appreciated!
Scott

[JoeM]

Is your birthday column always populated?
Are you returning valid numerical results for all records?

Note that unless you are only checking for people born within the past 60 days (including year), your formula won't work but it isn't taking into account the year part.
For example, if someone was born 10/1/1950, it is not going to return 22 days (10/1 to 10/23); rather it is going to return 23033 (the number of days between 10/1/1950 and 10/23/2013).

[Mtyetti2]

JoeM,

I discovered the error was because not all of the fields were returning numerical results. The fields are not actual birthdays. I was using that example for simplification. They are actually dates of recent doctor exams which expire at the end of the calendar month in 2 years. Some people do not have a doctor exam date, which is what is returning the error when it uses the calculation to figure out the expiration date. Bottom line: Is there a way to use this date subtraction expression for all the records even if some of them are blank? I'd like to keep the dates in the Date/Time format. I've heard of some people recommending to change it to a Text field, but I'd rather not do that if I don't need to.
Better yet, is there a way to return the work "Missing" for records that don't have a date? And not have this effect the criteria field calculation?? Hope this makes sense. Thanks a lot!!

[JoeM]

The issue is, if you try to return "Missing", that it going to coerce all results to Text, and you are going to have that "Data mismatch" error again.
Instead, if your date field is empty, return some large value that will never meet your criteria, i.e.

Date_Check: IIf(IsNull([Birthday]),999,Date()-[Birthday])

Then your criteria should work.