How to add 1 to a date formatted as yyyy?

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?

Thanks, John
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
the Format() function converts the result to Text type, since you are just extracting the year,
use the Year function instead of format

FirstCalcDate: FirstOrnamentYear: IIf(DatePart("m",[DateOfDeath])<11,Year([DateOfDeath]), Year(DateAdd("yyyy",1,[DateOfDeath])))
Rey Obrero (Capricorn1)Commented:
you can use the function CDate() to convert the calculated field to Date type
leachjAuthor Commented:
The calculated field returns only the year (i.e. 2012).  Using the cdate() function on the year only, returns full date range in the early 20th century (i.e. 07/06/1905)
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Rey Obrero (Capricorn1)Commented:
make sure that the calculated field is  Number type, use clng() or cint()

year(date()) returns a number data type

can you post the calculation done if n the query..

better if you can upload a sample db with the table and the queries
leachjAuthor Commented:
Tried Clng([FirstCalcDate]+1)  but same type mismatch in third query.
Here is the calculations from the First query.....

FirstCalcDate: FirstOrnamentYear: IIf(DatePart("m",[DateOfDeath])<11,Format([DateOfDeath],"yyyy"),Format(DateAdd("yyyy",1,[DateOfDeath]),"yyyy"))

SecondCalcDate: [FirstCalcDate] + 1  

Setting the criteria for a field in the third query to Year(Date()), for the SecondCalcDate throws the type mismatch error.

I can get it to work by using the first calculations and injecting a Dateadd() into it, but makes a long layered calculation.  Was looking for a shorter route.  Here is the modified calculation ....

leachjAuthor Commented:
As stated above, the FirstCalcDate works without error.  The SecondCalcDate where I just add 1 to the FirstCaclDate result ([FirstCaclDate] + 1), is the one that throws the error.
Rey Obrero (Capricorn1)Commented:
did you try the suggestion?
leachjAuthor Commented:
Yes, your solution worked.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.