# 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?

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

Projects Information Technology ManagerAuthor Commented:
Great
0

Commented:
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

Commented:
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
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.