Birthday & Age

[Andrew H]

Hi,

I have a database with the field 'Date Of Birth' and
another field below with a field 'Age' Does anyone know
the formula i can put into Age to make it work out the age
of the person as of current day and display it in the Age
Field?

Many Thanks

Andrew

 

[John Spencer (MVP)]

You can use the function below or you can extract the calculation from it.

Public Function fAge(dtmDOB, Optional dtmDate)
'Returns the Age in years, for dtmDOB.
'Age calculated as of dtmDate, or as of today if dtmDate is missing.

'If as of date not passed then set to today's date
If Not IsDate(dtmDate) Then dtmDate = Date

If IsDate(dtmDOB) Then 'If date passed, then calculate age
fAge = DateDiff("yyyy", dtmDOB, dtmDate) + _
(DateSerial(Year(dtmDate), Month(dtmDOB), Day(dtmDOB)) > dtmDate)
Else
fAge = Null
End If

End Function

Another method (Check this out as I often screw up the operator of the
comparison function in the IIF statement
DateDiff("yyyy",[Date of Birth], Date()) -
IIF(Format([Date of Birth],"mmdd") > Format(Date(),"mmdd"),1,0)

 

[John Vinson]

Hi,

I have a database with the field 'Date Of Birth' and
another field below with a field 'Age' Does anyone know
the formula i can put into Age to make it work out the age
of the person as of current day and display it in the Age
Field?

The Age field SHOULD NOT EXIST in your table: calculate it on the fly
instead, either in a Query or in the control source of a textbox on a
Form. The following expression works (just type this in a vacant Field
cell in a Query):

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