Calculate age using expression builder

How to calculate age using expression builder in query from date of birth given in table to return age in years and months only
Dr.Abdulwahab Al-anesiProjects Information Technology ManagerAsked:
Who is Participating?
Rey Obrero (Capricorn1)Commented:
place this in a column of your query

Age: DateDiff("yyyy",[DOB],Date()) & " Years " & DateDiff("m",DateSerial(Year(Date()),Month([DOB]),1),Date()) & " Months"

*DOB is the field for the date of birth
Dr.Abdulwahab Al-anesiProjects Information Technology ManagerAuthor Commented:
Dale FyeCommented:
I would prefer to use a function for this, passing in the birthday and, optionally, the date to compare it to.  Putting the logic for this in a query expression is a bit complex.

The first point is that you would use the DateDiff( ) function and the "m" argument to determine the number of months between the two dates.  However, you must take into account that because Jan 31 and Feb 1 are two different months, the results of DateDiff("m", #1/31/15#, #2/1/15#) = 1, so you must take into account more than just the DateDiff, you must compare the actual day to see whether the 2nd date value is >= the first.  

But this yields problems because months don't have the same # of days.  If your child was born on 1/31/15, at what point does he/she reach 1 month?  Is it Feb 28th? or is it 1 Mar?  You would have to decide how you want to handle that situation.  You could use 30 days as a standard, if you are not too concerned with accuracy.

You would also need to decide what format you want for your output.  Is: "1 year, 3 months" acceptable, or do you want something else?

A function that returns something like that might look like:

Public Function fnYrsAndMonths(DOB as Variant, Optional TestDate as Variant = NULL) as string

    dim intYears as integer
    dim intMonths as integer

    If IsNull(DOB) then
         fnYrsAndMonths = "No DOB provided"
         Exit Function
    End If

    TestDate = NZ(TestDate, Date())

    intMonths = DateDiff("m", DOB, TestDate)
    'Check for day
    if DateSerial(Year(DOB), Month(TestDate), Day(TestDate)) < DateAdd("m", 1, DOB) Then
        if intMonths > 0 then intMonths = intMonths - 1
    End if

    intYears = intMonths\12
    intMonths = intMonths - intYears*12

    fnYrsAndMonths = intYears & " year" & iif(intYears>1, "s ", " ")
    if intMonths > 0 then
        fnYrsAndMonths = fnYrsAndMonths & intMonths & iif(intMonths>1, "s", "")
    end if

End Function

Open in new window

In your query, to get the age as of today, you would just indicate:

AgeYrMo: fnYrsAndMonths([DOB])
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Dale FyeCommented:
be careful with the solution you accepted.

DateDiff("yyyy", #12/31/2014#, #1/1/2015#) will return 1 so right out of the gate that solution will provide incorrect year results a fair portion of the time.
Gustav BrockCIOCommented:
If you need 100% correct calculation, you have to use a solution that includes DateAdd like this:
Public Function Months( _
  ByVal datDate1 As Date, _
  ByVal datDate2 As Date, _
  Optional ByVal booLinear As Boolean) _
  As Integer

' Returns the difference in full months between datDate1 and datDate2.
' Calculates correctly for:
'   negative differences
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'   negative date/time values (prior to 1899-12-29)
' Optionally returns negative counts rounded down to provide a
' linear sequence of month counts.
' For a given datDate1, if datDate2 is decreased stepwise one month from
' returning a positive count to returning a negative count, one or two
' occurrences of count zero will be returned.
' If booLinear is False, the sequence will be:
'   3, 2, 1, 0,  0, -1, -2
' If booLinear is True, the sequence will be:
'   3, 2, 1, 0, -1, -2, -3
' If booLinear is False, reversing datDate1 and datDate2 will return
' results of same absolute Value, only the sign will change.
' This behaviour mimics that of Fix().
' If booLinear is True, reversing datDate1 and datDate2 will return
' results where the negative count is offset by -1.
' This behaviour mimics that of Int().

' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of months to dates of Feb. 29.
' when the resulting year is a common year.
' 2010-03-30. Cactus Data ApS, CPH.

  Dim intDiff   As Integer
  Dim intSign   As Integer
  Dim intMonths As Integer
  ' Find difference in calendar months.
  intMonths = DateDiff("m", datDate1, datDate2)
  ' For positive resp. negative intervals, check if the second date
  ' falls before, on, or after the crossing date for a 1 month period
  ' while at the same time correcting for February 29. of leap years.
  If DateDiff("d", datDate1, datDate2) > 0 Then
    intSign = Sgn(DateDiff("d", DateAdd("m", intMonths, datDate1), datDate2))
    intDiff = Abs(intSign < 0)
    intSign = Sgn(DateDiff("d", DateAdd("m", -intMonths, datDate2), datDate1))
    If intSign <> 0 Then
      ' Offset negative count of months to continuous sequence if requested.
      intDiff = Abs(booLinear)
    End If
    intDiff = intDiff - Abs(intSign < 0)
  End If
  ' Return count of months as count of full 1 month periods.
  Months = intMonths - intDiff
End Function

Open in new window

Dr.Abdulwahab Al-anesiProjects Information Technology ManagerAuthor Commented:
do you mean NO WAY to build expression in query that can return 100% correct result ?
is only possible to have it in events o forms.
if so shall forms be control source be from query or there is a way to have enter to the table directly.
can you help me further?
Gustav BrockCIOCommented:
No. It's a save-and-forget one-time operation.

Then use the function like this:

Select *, Int(Months([DoBfield], Date()) / 12) As AgeYears,  Months([DoBfield], Date()) Mod 12 As AgeMonths
From YourTable

Gustav BrockCIOCommented:
Or, for textboxes on a form or report:

    =Int(Months([DoBfield], Date()) / 12)
    =Months([DoBfield], Date()) Mod 12

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.