# Calculating Age In Access

Copper Contributor

# 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 not a death date I want to calculate it from today. Would I use a Calculated Field Type? What would I put in it?
I found some Vba code, but I don't know how to put that into my table.

6 Replies

# Re: Calculating Age In Access

Do NOT store the age as a calculated field in the table. People age one day at a time (heck, we age an hour at a time, a minute at a time). In that sense, we are never the same age from the moment we're born. The only time you could calculate an age reliably would be "age at death", or some other specific "As Of" date.

Rather, use that function to calculate the age in a query and DISPLAY it as needed. You can DISPLAY it in a control on a form or a report as well, using that calculation.

I see this is the same relational database application as in your other thread on table design. I should have picked upon the name field problem in answering it, but let's address it here.

It's important to resolve names into the smallest data points feasible. Here, at a minimum that would be FirstName and LastName, probably also MiddleName since there are artists like Jerry Lee Lewis who use all three....

# Re: Calculating Age In Access

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;

# Re: Calculating Age In Access

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

# Re: Calculating Age In Access

You could even modify it to return either "age at death" or "current age", using a Iif() or Switch()....

# Re: Calculating Age In Access

You could indeed ...

# Re: Calculating Age In Access

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