Calculating People's Ages

[Bob]

How do I set up a table field to calculate the record
individual's age, given today's date and that
individual's date of birth?

 

[Bas Cost Budde]

How do I set up a table field to calculate the record
individual's age, given today's date and that
individual's date of birth?

Not.

You don't store such a value. It is valid only today, and you can do
this calculation in a query or in a report if you need it.

The age calculation is spelled in Access as

DateDiff("yyyy", birthday, Date())

 

[John Vinson]

How do I set up a table field to calculate the record
individual's age, given today's date and that
individual's date of birth?

Don't.

If you store people's ages in a table, half of them on average will be
WRONG six months from now; every single one will be WRONG a year from
now.

Instead, store just the birthdate; in a Query or as the control source
of a Form or Report textbox you can calculate the age (as of the
moment you run the query) using the expression

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

The IIF() corrects the age by subtracting a year if the person's
birthday has not yet arrived.

 

[Duane Hookom]

Bas,
Do you really expect this to work well? This makes my 4 month old grandson 1
year old already. I would hate to lose 8 months of spoiling him.

 

[Bas Cost Budde]

Bas,
Do you really expect this to work well? This makes my 4 month old grandson 1
year old already. I would hate to lose 8 months of spoiling him.

I don't see the point, really. He will stay 1 year for almost 19 months!

Nah, wouldn't one who gets a utility (DateDiff in this case) read its
manual?

Er, okay. Probably not.

Dear OP, please see the Help for the function I suggested.

 

[Tim Ferguson]

How do I set up a table field to calculate the record
individual's age, given today's date and that
individual's date of birth?

Like everyone says, you don't even try to set it up as a column in a table.
If you want to calcuate it in a query or on a form, though, this might
help:

'
' Provide a helpfully formatted output of calculated age
'
Public Function IntelligentAge(BirthDate As Date) As String
Dim strAge As String
Dim dwAgeInDays As Long
Dim dblDateInUnits As Double
Dim dblBirthDateInUnits As Double

' Days is the only thing we can really bank on
dwAgeInDays = Date - DateValue(BirthDate)

' Okay, let's do some stratification
Select Case dwAgeInDays
Case Is < 15 ' up to two weeks

strAge = dwAgeInDays & " days"

Case 15 To 182 ' two weeks to six months

strAge = Int(dwAgeInDays / 7) & " weeks"

Case 183 To 730 ' six months to two years

dblDateInUnits = 12 * Year(Date) + Month(Date) + _
(Day(Date) / Day(DateSerial(Year(Date), Month(Date) + 1, 0)))

dblBirthDateInUnits = 12 * Year(BirthDate) + _
Month(BirthDate) + _
(Day(BirthDate) / Day(DateSerial(Year(BirthDate), _
Month(BirthDate) + 1, _
0)))

strAge = Format$(dblDateInUnits - dblBirthDateInUnits, _
"0.0 ""months""")

Case Is > 730 ' over two years
' decimal ages are used in medicine a lot: but you
' can simplify this if you want whole numbers of years
'
' calculate a decimal date for today
dblDateInUnits = Year(Date) + _
(CDbl(Format$(Date, "y")) / _
CDbl(Format$(DateSerial(Year(Date) + 1, 1, 0), "y")))

' calculate a decimal date for the birthdate
dblBirthDateInUnits = Year(BirthDate) + _
(CDbl(Format$(BirthDate, "y")) / _
CDbl(Format$(DateSerial(Year(BirthDate) + 1, 1, 0), "y")))

' subtract them for a decimal age
strAge = Format$(dblDateInUnits - dblBirthDateInUnits, "0.0 ""years""")

End Select

' return the formatted value
IntelligentAge = strAge

End Function



Hope that helps


Tim F