Handling Leap Years

[ricky]

Good morning everyone,

Just a quick question, which I am sure is quite trivial, but I am not sure
how to handle Leap Years.

I have an Access form, which contains details about motor policies.

I also have a command button - "Renew", which when clicked, increments the
"Start Date" by one year and the "End Date" by one year, minus one day.

For example, this is what I get:

Before Renewal:
Start Date = 01/MAR/2006
End Date = 28/FEB/2007

After Renewal:
Start Date = 01/MAR/2007
End Date = 27/FEB/2007

***************************************************
However next year is a leap year, so I need to show the following:
After Renewal:
Start Date = 01/MAR/2007
End Date = 29/FEB/2007

Any ideas?

Kind regards

Ricky

 

[Rick Brandt]

Good morning everyone,

Just a quick question, which I am sure is quite trivial, but I am not
sure how to handle Leap Years.

I have an Access form, which contains details about motor policies.

I also have a command button - "Renew", which when clicked,
increments the "Start Date" by one year and the "End Date" by one
year, minus one day.

For example, this is what I get:

Before Renewal:
Start Date = 01/MAR/2006
End Date = 28/FEB/2007

After Renewal:
Start Date = 01/MAR/2007
End Date = 27/FEB/2007

***************************************************
However next year is a leap year, so I need to show the following:
After Renewal:
Start Date = 01/MAR/2007
End Date = 29/FEB/2007

Any ideas?

Kind regards

Ricky

StartDate = DateSerial(Year(StartDate)+1, Month(StartDate), 1)
EndDate = DateSerial(Year(EndDate)+1, Month(EndDate)+1, 0)

With DateSerial the zeroth of a month is the last day of the previous month and
that works across year boundaries and with leap years.

 

[Douglas J. Steele]

Ignoring the fact that next year is 2008 (not 2007, as in your example), the
built-in date functions are capable of handling it for you:

?DateAdd("d", -1, DateAdd("yyyy", 1, #1 Mar 2007#))
2/29/2008


DateAdd("d", -1, DateAdd("yyyy", 1, [Start Date]))

will return the correct value for you.

 

[ricky]

thanks guys for the suggestions..

LOL - Well spotted Doug, thanks.

Kind regards

Ricky

Ignoring the fact that next year is 2008 (not 2007, as in your example), the
built-in date functions are capable of handling it for you:

?DateAdd("d", -1, DateAdd("yyyy", 1, #1 Mar 2007#))
2/29/2008


DateAdd("d", -1, DateAdd("yyyy", 1, [Start Date]))

will return the correct value for you.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Good morning everyone,

Just a quick question, which I am sure is quite trivial, but I am not sure
how to handle Leap Years.

I have an Access form, which contains details about motor policies.

I also have a command button - "Renew", which when clicked, increments the
"Start Date" by one year and the "End Date" by one year, minus one day.

For example, this is what I get:

Before Renewal:
Start Date = 01/MAR/2006
End Date = 28/FEB/2007

After Renewal:
Start Date = 01/MAR/2007
End Date = 27/FEB/2007

***************************************************
However next year is a leap year, so I need to show the following:
After Renewal:
Start Date = 01/MAR/2007
End Date = 29/FEB/2007

Any ideas?

Kind regards

Ricky