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

ricky wrote:
> 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.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com 

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
http://I.Am/DougSteele
(no e-mails, please!)


"ricky" <ricky@ricky.com> wrote in message 
news:e1brIGKYHHA.984@TK2MSFTNGP04.phx.gbl...
> 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
>
> 

thanks guys for the suggestions..

LOL - Well spotted Doug, thanks.

Kind regards

Ricky

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:OP4LIULYHHA.4440@TK2MSFTNGP03.phx.gbl...
> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "ricky" <ricky@ricky.com> wrote in message
> news:e1brIGKYHHA.984@TK2MSFTNGP04.phx.gbl...
> > 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
> >
> >
>
>