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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dr.Abdulwahab Al-anesiProjects Information Technology ManagerAuthor Commented:
Dale FyeOwner, Developing Solutions LLCCommented:
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])
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dale FyeOwner, Developing Solutions LLCCommented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.