Datediff results in criteria expression
Posted on 2012-08-23
I have an Access query in which I have an expression that calculates the number of months since a person's employment was verified by using the DateDiff function. The field that contains the date of the employment verification is named "EMPLOYMENT_DATE". The expression that determines the number of months since the last verification is:
This returns what looks like an integer value, and the values appear correct. The problem is that I want to see only those records where the number of months is >6. If I put >6 on the criteria line of the query I get a data type mismatch error. I have tried a variety of things including wrapping EMPLOYMENT_DATE in DateValue(EMPLOYMENT_DATE), and although that appears to produce the same integer value, I can't put the criteria on it either without throwing the data type mismatch error.
I have read the various postings in the knowledge base, but have not found anything that addresses this directly,
Thanks in advance for your help.