DateAdd error in MS Office 2010

[Noah4x4]

I have two controls, [Inception Date] and [Expiry Date] for for an insurance policy application. So Insert Inception Date and the Expiry Date is calculated. I am here using (as +365 days isn't always correct).



Dim Tempdate
Tempdate = [Inception Date]
[Expiry Date] = DateAdd ("Yyyy", 1, "Tempdate")

Enter 1 March 2011 and this returns 29 Feb 2012.
At first glance, this seems to be correct for an insurance policy as expiry is always one day befor renewal. However;
Enter 1 March 2012 and it now returns 1 March 2013.

I have tried
[Expiry Date] = DateAdd ("Yyyy", 1, "Tempdate") - 1
Also
[Expiry Date] = (DateAdd ("Yyyy", 1, "Tempdate") - 1)

An each time when I enter 1 march 2012 and it returns 1 March 2013. Surely one of these should result in a day earlier? I thought DateAdd handled leap years OK? What am I missing?

Help please? BTW, I am using the (date picker) - is that the same as the 'calendar control', mentioned in the previous thread as no longer supported? Does it hence have a bug? If yes, is ait available under Date/Time properties?

[Noah4x4]

BTW, I am using the 'date picker' tool. Is there some issue with this (see previous thread)? If yes, why is it still a feature of an out of the box copy of Access 2010 purchased only last week?

[pkstormy]

Wouldn't this line...

[Expiry Date] = DateAdd ("Yyyy", 1, "Tempdate")

be

[Expiry Date] = DateAdd ("Yyyy", 1, #Tempdate#)

Just a thought.

[Noah4x4]

Tried that
[Expiry Date] = DateAdd ("Yyyy", 1, #Tempdate#)

The # signs create a compile error - am sure it is "Tempdate" (am also following code guidelines in a book)

But what I completely don't understand is why DateAdd ("Yyyy", 1, "Tempdate") returns 29th Feb 2012 when Tempdate is 1st March 2011, yet returms 1st March 2013 if Tempdate is 1st March 2012.

I can see one is a leap year, but surely if the first returns 29th Feb the second should return 28th Feb if adding a calender year. Else what is the advantage over simply adding + 365 to Date()?

One returns a day less. I thought DateAdd auto-adjusted for leap years?

[Noah4x4]

Doh....I am an idiot

Worked it out now.

2013 isn't a leap year although 2012 is...arrrgggh.my head hurts from too many run time errors

[RuralGuy]

That's great! I guess you are now ready to use the Thread Tools and mark this thread as Solved, yes?

[pkstormy]

I thought DateAdd auto-adjusted for leap years?

Yes - I believe it does (or should).

[Noah4x4]

It does!

What I could not get my head around was that if an insurance policy started on 1st March, it always has a renewal date of 1st March. So if incepted in 2011, it expires on 29th February, and if incepted in 2012 it expires on 28th Feb...fair enough... always the day before.

But a policy incepted on 29th February 2012 and another incepted 1st March 2012 must BOTH have the same renewal date of 1st March 2013 and an expiry date of 28th February.

My brain hurt trying to work out why the expiry date result for both was exactly the same when normal mathematical logic suggested one should be a day earlier (reason is 2013 isn't a leap year and 29th february doesn't exist)......doh!