How do you calculate age from a birth date in MS Access?

How do you calculate the current age of an individual based off of their birthdate in MS Access?

I've tried =DateDiff("yyyy", [DOB], Date())

But that only returns how old they will be that calendar year, so if they haven't had their birthday yet then it's inacurate. How can you calculate their age based of what calendar day it is?

1 Answer

There's a function you can add to your code that does this easily for you. Go to Visual Basic, insert a new module and then paste in the following code:

Function Age(varDOB As Variant, Optional varAsOf As Variant) As Variant 'Purpose: Return the Age in years. 'Arguments: varDOB = Date Of Birth ' varAsOf = the date to calculate the age at, or today if missing. 'Return: Whole number of years. Dim dtDOB As Date Dim dtAsOf As Date Dim dtBDay As Date 'Birthday in the year of calculation. Age = Null 'Initialize to Null 'Validate parameters If IsDate(varDOB) Then dtDOB = varDOB If Not IsDate(varAsOf) Then 'Date to calculate age from. dtAsOf = Date Else dtAsOf = varAsOf End If If dtAsOf >= dtDOB Then 'Calculate only if it's after person was born. dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB)) Age = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf) End If End If
End Function

You can then simply create a control and set the control source to =Age([name of the filed with the birth date]) and the function will accurately calculate ages.

^{Code Provided by Allen Browne.}