Forum Discussion
Calculating Age In Access
Hi All, Just got a question regarding calculating ages in tables in access. I am on Access 2016, and I have two columns, DOB, and Death Date. I only have one entry for a death date, and if there is...
here is another function:
Function fnAge(dtmBD As Variant, Optional dtmDate As Variant = 1) _
As Variant
' Calculate a person's age, given the person's birth date and
' an optional "current" date.
If IsNull(dtmBD) Or Not (IsDate(dtmBD)) Then
Exit Function
End If
If IsNull(dtmDate) Then dtmDate = 1
If dtmDate = 1 Then
' Did the caller pass in a date? If not, use
' the current date.
dtmDate = Date
End If
fnAge = DateDiff("yyyy", dtmBD, dtmDate) + _
(dtmDate < DateSerial(Year(dtmDate), Month(dtmBD), _
Day(dtmBD)))
End Function
you create a Query against your table:
select artist_id, artist_name, birth, death, fnAge([Birth], [Death]) As Age from yourTableName;
Function fnAge(dtmBD As Variant, Optional dtmDate As Variant = 1) _
As Variant
' Calculate a person's age, given the person's birth date and
' an optional "current" date.
If IsNull(dtmBD) Or Not (IsDate(dtmBD)) Then
Exit Function
End If
If IsNull(dtmDate) Then dtmDate = 1
If dtmDate = 1 Then
' Did the caller pass in a date? If not, use
' the current date.
dtmDate = Date
End If
fnAge = DateDiff("yyyy", dtmBD, dtmDate) + _
(dtmDate < DateSerial(Year(dtmDate), Month(dtmBD), _
Day(dtmBD)))
End Function
you create a Query against your table:
select artist_id, artist_name, birth, death, fnAge([Birth], [Death]) As Age from yourTableName;
AFAIK, the simplest expression for calculating age in years to the current date is:
Function AgeYears(DOB As Date) As Integer AgeYears = DateDiff("yyyy", DOB, Date) + (Format(DOB, "mmdd") > Format(Date, "mmdd")) End FunctionIt could easily be adapted to allow for date at death as above
You could even modify it to return either "age at death" or "current age", using a Iif() or Switch()....
You could indeed ...
