DataAdd is giving a Data mismatch error

[Eli]

I am running a query that returns the employee's review date using a
formula based on the hiredate: See code:

ReviewDate:
DateAdd("d",Date()-[tblEmployeeList]![HireDate],[tblEmployeeList]![HireDate])

However, when I run the query I get the message "Data type mismatch in
criteria expression" If I hard-code the number of days, it works. Any
ideas. Thanks!

 

[Jeff Boyce]

Eli

If I'm reading your statement correctly, you are adding the number of days
between a field named [HireDate] and today's date to [HireDate]. Doesn't
that equal today's date?

By the way, even though the field is named [HireDate], there's no guaranty
that the data type is a date/time ... and this could be the cause of the
mismatch error message.

 

[Eli]

Well, that sure looked like it worked on Friday.

Anyway, you're right. So I've altered the formula. I am now
calculating a field to determine the length of service (Lservice) See
code:

Lservice: DateDiff("yyyy",[tblEmployeeList]![HireDate],Date())

And then I am calculating the review date in another query. See code:

Review:
DateAdd("yyyy",[qryLengthofService]![Lservice],[tblEmployeeList]![HireDate])

However, I am still getting the same Data Type Mismatch Error. I've
checked the data type of HireDate, it is date/time. And again, if I
hard code the Lservice I do not get the error. See code:

Review: DateAdd("yyyy",1,[tblEmployeeList]![HireDate])

Any ideas? Thanks!

 

[Eli]

I discovered the error. The field [HireDate] contained Null values. I
set the criteria to Is Not Null and worked fine. Thanks for the help!