[AccessD] Age calculation

Hi Susan

> That's not an error -- you can't expect date arithmetic to apply a
> "business" rule, and frankly, that's what it is. The expression is returning
> the correct answer, it just isn't the answer you require. 

It's not me and it's not a business rule. It's the answer to the question: 
Which date to pick if the date (Feb. 29.) does not exist? The day before or the day after? 
As the day after falls in a different month, the logical answer is to pick the day before.
This is exactly what DateAdd() does:

  DateAdd("yyyy", 1, #2/29/2000#) 

which returns 2001-02-28.

And for a reason. You may hear the argument, that a "full year", 365 days, must pass to be one year older, thus a leapling must pass Feb. 28. for any year which means Feb. 29 in leap years and Mar. 1. in common years.
However, those people haven't done their home work. Consider these examples using the expression:

  ? DateDiff("d", d1, d2)

d1 = #1/28/2001#
d2 = #1/28/2002#
Result: 365

d1 = #10/28/2001#
d2 = #10/28/2002#
Result: 365

d1 = #1/28/2000# (leap year)
d2 = #1/28/2001#
Result: 366

d1 = #10/28/2000# (leap year)
d2 = #10/28/2001#
Result: 365

d1 = #2/28/2000# (leap year)
d2 = #2/28/2001#
Result: 366

d1 = #2/29/2000# (leap year)
d2 = #2/28/2001#
Result: 365

d1 = #2/29/2000# (leap year)
d2 = #3/1/2001#
Result: 366

You'll see, that only those born before Feb. 29. in a leap year will have 366 days until their birthday of the following year, while the majority (as the Oct. example) will have 365 days - as will those of birthday Feb. 29. - thus nothing indicates why the leaplings should wait one day, increasing the count of days from 365 to 366, to celebrate their birthday. In fact, the only argument seems to be a question of personal preference which is non-arguable and fully valid though useless for business purposes or public authorities.

> Seems like it would be easy enough to run a simple check -- if it's a Feb 29
> birthdate and the date is Feb 28, add 1. 

Yes, that's what my function does - of course for common years only.

/gustav

>>> ssharkins at setel.com 26-06-2007 00:39 >>>
That's not an error -- you can't expect date arithmetic to apply a
"business" rule, and frankly, that's what it is. The expression is returning
the correct answer, it just isn't the answer you require. 

Seems like it would be easy enough to run a simple check -- if it's a Feb 29
birthdate and the date is Feb 28, add 1. 

Susan H. 

Hi Susan

No, same error.

/gustav

>>> ssharkins at setel.com 25-06-2007 15:48 >>>
I came up with this one years and years ago -- will it work for you?

Year(Now())-Year(date)+(DateSerial(Year(Now()),Month(date),Day(date))>Now())

Susan H. 

Hi Steve

Sorry, it still fails miserably for, say, these dates:

DOB = #2/29/1992#
AtDay = #2/28/1997#

returning 4 and not 5.

/gustav

>>> miscellany at mvps.org 23-06-2007 22:57 >>>
Hehe.  Answering my own question.  I wouldn't mind $5 for every time I have
used that expression over the last 12 years, on the assumption that I had it
licked.

And now I see the obvious - in the case of a 29 February birthday,
evaluating age on 28 February in a non leap year works correctly, but
evaluating age on 28 February during a leap year, it gives the wrong figure.

So, I have modified accordingly...

Age: DateDiff("yyyy",[DOB],[DateAt])+(Format([DOB]+(Format([DOB],"mmdd")
=Format([DateAt],"mmdd")="0229"),"mmdd")>Format([DateAt],"mmdd"))

Regards
Steve


Steve Schapel wrote:
> However, I can't find an example where the following does not work 
> correctly.  Can you?
> 
> Age: 
> DateDiff("yyyy",[DOB],[DateAt])+(Format([DOB]+(Format([DOB],"mmdd")
> ="0229"),"mmdd")>Format([DateAt],"mmdd"))
--