Age function

IEHP1
IEHP1 used Ask the Experts™
on
So I have a date/time field for the birth date called birth. I need to create a function that will calculate a person's age as of the present time. Please let me know if you can help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:

Author

Commented:
I'm not sure if that really helps out what I am trying to do. I created a VBA module and pasted in the code, then tried putting in the field as Age: AgeSimple([birth]) since it is one of the fields in the query.

I am trying to pull this information into the data pull for a query. I believe first I would need to convert the number of years, months, days from their birthdate to the present time (the number of months or months and days from birthday) (how many months from their birthday so that I can in a subsequent step, create a nested IIf statement for the age groups).....

Please let me know if you are understanding what I am trying to do. Thank you.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You did it right:

Age: AgeSimple([birth])

There is nothing more in it, except if you fields of Birth with Null value. If so, filter those out.

/gustav
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Actually, Expr2: DateDiff("d",[birth],Date()) is what will give me the number of days from their birthdate. So now I will have to create the nested IIf statement for the age groups based on my results from that query.
Top Expert 2009
Commented:
Here is a simple Age function:

Public Function Age(varBirthDate As Variant) As Integer
'From Microsoft Help
'Last modified by Helen Feddema 12-Jun-2010

On Error GoTo ErrorHandler

   Dim varAge As Variant

   If IsNull(varBirthDate) Then
      Age = 0
      GoTo ErrorHandlerExit
   Else
      varAge = DateDiff(interval:="yyyy", _
         date1:=varBirthDate, _
         date2:=Now)
      
      If Date < DateSerial(Year:=Year(Now), _
         Month:=Month(varBirthDate), _
         Day:=Day(varBirthDate)) Then
         varAge = varAge - 1
      End If
      
      Age = CInt(varAge)
   End If
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in Age procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit
       
End Function

Open in new window

Top Expert 2009

Commented:
You can set up a Select Case statement to get your age groups, depending on the value of Age.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
> So now I will have to create the nested IIf statement for the age groups based on my results

No, that won't work because years are not of the same count of days. Neither will the method using DateSerial do as it doesn't take leaplings (those born on Feb. 29th) in account.
You have to use DateAdd as shown above in the function AgeSimple to get it 100% right.

You don't specify your age groups, but if they are decades you can do:

AgeGroup: 1 + AgeSimple([birth]) \ 10

Note the use of backslash to get an integer result for all ages.

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial