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 Function
It 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 ...
For info, I've added a new page to my website Calculate Age (isladogs.co.uk).
It includes various functions to calculate age in years, years and months or years, months and days.
Calculations can be done to the current date or between two specified dates
