I am using Access 2007. I have 3 queries.
In the first query, I calculate 2 dates based on a date of death. The first calculated field determines what year an action takes place and the result is formatted as "yyyy". In the second calculated field, I just add 1 to that result ([FirstCalcDate] + 1) which returns the year incremented by one.
In the second query is based on the first and I check to see if the [FirstCalcDate] is equal to the current year with the date function ( Year(Date()) ). That works without problem.
In the third query is also based on the first and I do the check to see if the [SecondCalcDate] field is equal to the current year with the same function Year(Date()). This fails with a data type mismatch.
The problem seems to be, when I add the 1 in the first query it apparently changes the data type and the Year(Date()) function does not recognize [SecondCalcDate] as a date, but a number. I tried converting it to string to no avail.
Finally the question. Is there a way in the first query to take the result of the [FirstCalcDate] and increment it retaining it as a date so the function Year(Date()) will recognize it as a date?