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
leachjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you can use the function CDate() to convert the calculated field to Date type
0
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)
0
Rey Obrero (Capricorn1)Commented:
ok..
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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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 ....

IIf(DatePart("m",[DateOfDeath])<11,Format(DateAdd("yyyy",1,[DateOfDeath]),"yyyy"),Format(DateAdd("yyyy",2,[DateOfDeath]),"yyyy"))
0
Rey Obrero (Capricorn1)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])))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
Rey Obrero (Capricorn1)Commented:
did you try the suggestion?
0
leachjAuthor Commented:
Yes, your solution worked.

Thanks,
John
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.