Age Calculator

[bhipwell via AccessMonster.com]

I need a formula to determine the age of an individual to be used for various
calculations. The "age" is may not be the age of the person today, but as of
a certain date. The "todays date" in the examples below is actual date
Access may be making the calculation.

- Example #1:
Birthday: 11/15/1970
Target Date: 02/01/2009
Todays Date: 04/01/02009
In this example, the real age is 39 and the "calculated age" is 39 as well.

Example #2:
Birthday: 11/15/1970
Target Date: 10/01/2008
Todays Date: 04/01/2009
Here the real age is 39, but the "calculated age" is 38 since the person was
38 before the target date came.

Example #3:
Birthday: 03/01/1970
Target Date: 04/01/2008
Today's Date: 03/15/2009
Here the real age is 39, but the "calculated age" is 37 since the today fell
between the birthday and the target date.

Calculating the age this was will be used to determine insurance rates in
real time. Because "today's date" changes everyday, a person's age compared
to when the individual first entered the insurance plan doesn't stay static.


Other ideas would be helpful too.

BH

 

[John Spencer MVP]

Here are 4 possibilities

Public Function fAge(dtmDOB, Optional dtmDate)
'Returns the Age in years, for dtmDOB.
'Age calculated as of dtmDate, or as of today if dtmDate is missing.

If Not IsDate(dtmDate) Then dtmDate = Date 'If as of date not passed then
set to today's date

If IsDate(dtmDOB) Then 'If date passed, then calculate age
fAge = DateDiff("yyyy", dtmDOB, dtmDate) + (DateSerial(Year(dtmDate),
Month(dtmDOB), Day(dtmDOB)) > dtmDate)
Else
fAge = Null
End If

End Function

In the following replace Date() with a reference to your targetDate field.

'Fails if DOB is null
CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd"))

'Returns Null if DOB is Null
CurrentAge =DateDiff("yyyy",DOB,Date()) + Int(Format(DOB,"mmdd") >
Format(Date(),"mmdd"))

DateDiff("yyyy",DOB,Date()) + Format(DOB,"mmdd") > Format(Date(),"mmdd")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County