# 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
###### Who is Participating?

x
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.

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
0

Experts Exchange Solution brought to you by

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

Projects Information Technology ManagerAuthor Commented:
Great
0
Owner, 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
``````
In your query, to get the age as of today, you would just indicate:

AgeYrMo: fnYrsAndMonths([DOB])
0
Owner, 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.
0
CIOCommented:
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)
Else
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
``````
/gustav
0
Projects Information Technology ManagerAuthor Commented:
Dears
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?
0
CIOCommented:
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
0
CIOCommented:
Or, for textboxes on a form or report:

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

/gustav
0
###### 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.