Leap year date calculation

[Rachel]

I have a form we use for tracking annual policy
information. We enter the effective date and it
calculates the expiration date by adding 365 days.
Because of this being a leap year, any policy term that
crosses over 2/29/04 has an expiration date that is 1 day
short. This is causing all kinds of problems. Does
anyone have a solution? Here is the current code:
Private Sub WC_Exp_Can_Enter()
If [wc eff] > 0 Then
[wc exp/can] = [wc eff] + 365
End If
End Sub

 

[Rick Brandt]

I have a form we use for tracking annual policy
information. We enter the effective date and it
calculates the expiration date by adding 365 days.
Because of this being a leap year, any policy term that
crosses over 2/29/04 has an expiration date that is 1 day
short. This is causing all kinds of problems. Does
anyone have a solution? Here is the current code:
Private Sub WC_Exp_Can_Enter()
If [wc eff] > 0 Then
[wc exp/can] = [wc eff] + 365
End If
End Sub

Use the DateAdd() function. It takes leap years into account.

Private Sub WC_Exp_Can_Enter()
If [wc eff] > 0 Then
[wc exp/can] = DateAdd("yyyy", 1, [wc eff])
End If
End Sub

 

[Andi Mayer]

I have a form we use for tracking annual policy
information. We enter the effective date and it
calculates the expiration date by adding 365 days.
Because of this being a leap year, any policy term that
crosses over 2/29/04 has an expiration date that is 1 day
short. This is causing all kinds of problems. Does
anyone have a solution? Here is the current code:
Private Sub WC_Exp_Can_Enter()
If [wc eff] > 0 Then
[wc exp/can] = [wc eff] + 365
End If
End Sub

add 1 to the year like:

[wc exp/can] =
dateserial(year([wc eff]) + 1,month([wc eff]),day([wc eff])

if month([wc eff])=2 AND day([wc eff])=29 then
[wc exp/can] = [wc exp/can] -1
' the 29th of a nonLeapYear would be the 1 of march
endif

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

 

[James Goodman]

What about:

Private Sub WC_Exp_Can_Enter()
If [wc eff] > 0 Then
[wc exp/can] = DATEADD("yyyy", 1, [wc eff])
End If
End Sub

I think this will alleviate your problem...

 

[Rachel]

It worked!!! You guys are awesome. This was driving me
nuts.. Thank you very much
Rachel

-----Original Message-----
What about:

Private Sub WC_Exp_Can_Enter()
If [wc eff] > 0 Then
[wc exp/can] = DATEADD("yyyy", 1, [wc eff])
End If
End Sub

I think this will alleviate your problem...

--
James Goodman

I have a form we use for tracking annual policy
information. We enter the effective date and it
calculates the expiration date by adding 365 days.
Because of this being a leap year, any policy term that
crosses over 2/29/04 has an expiration date that is 1 day
short. This is causing all kinds of problems. Does
anyone have a solution? Here is the current code:
Private Sub WC_Exp_Can_Enter()
If [wc eff] > 0 Then
[wc exp/can] = [wc eff] + 365
End If
End Sub

.