I am trying to calculate the age of someone on a form and a report, based on the birthdate field (ex: 06/19/1975). I need to be able to display the age in a text field on the entry form right after the person enters the birthdate on the entry form and then be able to run a report from another menu that will check for anyone over 60, based on the birthdate field and todays date. Any ideas? Datepart function seems to not be working for me. Thanks.
Age could be either a control or variable (for my app, it was the name of a control on the form).
DOB is the Date of Birth
Now is a canned function in Access
365.25 is the constant to counter leap years
I had an interesting problem with the date() function returning,.............well nothing!!. This forced me to switch to the Now() function. The formula above seemed to work 99.999% of the time (I didn't get a wrong age once, but I'm sure there is one case where it could).
Here is a function to calculate age. The code could be placed in any module.
An example of use for a control's ControlSource property would be:
An example of use for a calculated field in a query would be:
AgeCalcField: AgeInYears([DOBField], Date())
Public Function AgeInYears(ByVal pvarBirthDate As Variant, ByVal pvarCompareDate As Variant) As Long
' Purpose: Calc age in years as of the compare date.
' Accepts: pvarBirthDate date
' pvarCompareDate date
' Returns: number
' Returns 0 if any needed fields blank.
' A simple DateDiff("yyyy",[pvarBirthDate],[pvarCompareDate]) is off
' by one when the month & day of the birthdate is greater than
' the month & day of the compare date.
' Access stores the date AND time in each of the date/time fields.
' So even though the fields may be formatted to LOOK just like
' date only, there also is a time stored.
' And the time stored is usually a constant 12:00:00 AM
' (effectively 0) which can be seen if you set the field to be
' long time format. Or even in General Date format any time but
' 12:00:00 AM is shown. It's usually a good idea to store a current date
' using Date() instead of Now() unless one really does want a time
' other than 12:00:00 AM to be stored.
On Error GoTo Err_AgeInYears
' Exit if some fields null.
If IsNull(pvarBirthDate) Or IsNull(pvarCompareDate) Then
AgeInYears = 0
' Calc age in years and subtract 1 if the birth month/day is not on or before the
' compare month/day.
' (I.e. 8/1/1995 birthdate and 9/1/1996 compare date would be age of 1
' and 9/1/1995 birthdate and 9/1/1996 compare date would be age of 1
' but 9/2/1995 birthdate and 9/1/1996 compare date would be age of 0.)
If (Month(pvarBirthDate) < Month(pvarCompareDate)) Or (Month(pvarBirthDate) = Month(pvarCompareDate) And Day(pvarBirthDate) <= Day(pvarCompareDate)) Then
AgeInYears = DateDiff("yyyy", pvarBirthDate, pvarCompareDate)
AgeInYears = DateDiff("yyyy", pvarBirthDate, pvarCompareDate) - 1
MsgBox "Error " & Err & "." & Chr(13) & Chr(10) & Chr(10) & Err.Description & ".", vbExclamation