Subtract one year accurately?

I have a timecard logging program in Access97 that I've used for a few
years now. Most of the year it works without problems. But...

Every January, when logging in the December cards, the data entry
person has to start including the year when typing, otherwise the card
is logged as NEXT December.

I started putting together a simple AfterUpdate function to reduce
keying errors, and it basically looks like this:

if [TCDate]>Now() then
[TCDate]=[TCDate]-365
endif

But then I realized that leap years will cause a one-day-off error.
Rather than re-invent the wheel, I'm hoping one of you nice people can
tell me a simpler way to subtract exactly one year from a keyed-in
date. Is there some sort of named function that I can use in place of
the "-365" bit, rather than write a long IF statement to test for
'year is divisible by 4' except when it's divisible by 100, etc.

I'm pretty sure I'll read the responses and slap my forehead, but I
can live with that. :)

Thanks in advance for any help!

Dennis

re: Subtract one year accurately?

On 10 Feb 2004 07:20:34 -0800, Dennis Kuhn wrote:
[color=blue]
> I have a timecard logging program in Access97 that I've used for a few
> years now. Most of the year it works without problems. But...
>
> Every January, when logging in the December cards, the data entry
> person has to start including the year when typing, otherwise the card
> is logged as NEXT December.
>
> I started putting together a simple AfterUpdate function to reduce
> keying errors, and it basically looks like this:
>
> if [TCDate]>Now() then
> [TCDate]=[TCDate]-365
> endif
>
> But then I realized that leap years will cause a one-day-off error.
> Rather than re-invent the wheel, I'm hoping one of you nice people can
> tell me a simpler way to subtract exactly one year from a keyed-in
> date. Is there some sort of named function that I can use in place of
> the "-365" bit, rather than write a long IF statement to test for
> 'year is divisible by 4' except when it's divisible by 100, etc.
>
> I'm pretty sure I'll read the responses and slap my forehead, but I
> can live with that. :)
>
> Thanks in advance for any help!
>
> Dennis[/color]

Dennis,
Don't slap too hard.. :-)

[TCDate]=DateAdd("yyyy",-1,[TCDate])

See VBA Help for info on DateAdd().
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

re: Subtract one year accurately?

fredg <fgutkind@att.net> wrote in message news:<iwb8pbzfzkpj$.44cvm9tyu5ie.dlg@40tude.net>.. .[color=blue]
>
>
> Dennis,[/color]
[color=blue]
> Don't slap too hard.. :-)
>
> [TCDate]=DateAdd("yyyy",-1,[TCDate])
>
> See VBA Help for info on DateAdd().[/color]


D'oh! [Slap!!]

The worst part is that I _did_ see the DateAdd bit, but after a
cursory glance at the help for it, I assumed it wasn't really what I
was looking for. In actuality, I was only looking for one specific
type of solution, and didn't accept that this was a valid alternative.
Gotta loosen up this old brain of mine...

Oh, thanks very much!

Dennis