Faulty Datediff (Access 2003)

[Henning]

Given a Birthdate [DOB], i wish to calculate age in years(0..), month
(0..11), weeks(0..3) and days(0..6). My method of choise i
Datediff("d";[DOB];Now()), from wich all can be calculated by a little math.
However there seems to be a problem in Datediff. If i.e [DOB]=23/04-2001 and
Now()=12/06-2005, then the differens in days (in a 360 day calender) is 1489
days. However Datediff returns 1511 days.

Any ideas as to why this error exists, and how to fix it?

 

[Arvin Meyer]

There are 365 days in a year (leap years are 366), not 360 and DateDiff uses
the 365. 1511 is the correct return.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[fredg]

Given a Birthdate [DOB], i wish to calculate age in years(0..), month
(0..11), weeks(0..3) and days(0..6). My method of choise i
Datediff("d";[DOB];Now()), from wich all can be calculated by a little math.
However there seems to be a problem in Datediff. If i.e [DOB]=23/04-2001 and
Now()=12/06-2005, then the differens in days (in a 360 day calender) is 1489
days. However Datediff returns 1511 days.

Any ideas as to why this error exists, and how to fix it?

Your math is faulty. So is your calendar. ;-(
My calendar shows 365 1/4 days (not 360), which is what DateDiff()
computes.

Let's just assume we're adding 365 days for each year from 23, Apr
2001 to 23 Apr, 2005. That's 1460 days. There is 1 leap year day in
that period, so now we have 1461.
From 23 Apr, 2005 to 23 May, 2005 is 30 days. Now we're up to 1491.
From 23 May to 12 June is 20 days (as May has 31 days). So now we're
up to 1511.
DateDiff("d",#23/04/2001#,#12/6/2005#) = 1511

An accurate expression to calculate a person's age (in years) is:

In a query:
Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

Directly as the control source of an unbound control:
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)

The above expression takes into account whether or not the birth date
has already occurred in the current year.

You do know, I hope, that this Age computation should NOT be stored in
any table.
Just compute it and display it on a form or report, as needed.

If you need to compute the age in Years, Months, and Days,
i.e. 35 Years, 4 Months, 25 Days, see:
Check "A More Complete DateDiff Function" at
http://www.accessmvp.com/djsteele/Diff2Dates.html