Calculating Age

Question

Interesting dilema here.  Using a calcuated field to determine a patients age using the following formula [arrdate]-[dob]/365.25.  when I leave the result type to decimal the age is there and correct with a ridiulosly large fraction and when I change it to an integer, it's displaying an inaccurate age becuase it is rounding up.  Example:  DOB is 9/3/1999 Arrival date is 7/21/2011.  Pt is 11 years old.  When displayed as a decimal his age is 11.879534565366187542778918549.  When displayed as an integer his age is 12.  How can I tell Access NOT to round up?

Thanks for any input you can give.

-Greg

Answer 1

Don't calculate age by division, it's not a reliable method.  To return age in years use a function like this:

Public Function GetAge(varDoB As Variant, Optional varAgeAt As Variant) As Variant

    If IsMissing(varAgeAt) Then varAgeAt = VBA.Date

    GetAge = DateDiff("yyyy", varDoB, varAgeAt) - _

        IIf(Format(varAgeAt, "mmdd") < Format(varDoB, "mmdd"), 1, 0)

End Function

To return age in Years:Months use this function:

Public Function GetAgeYearsMonths(varDoB As Variant, Optional varAgeAt As Variant) As Variant

    Dim intYears As Integer

    Dim intMonths As Integer

    If IsMissing(varAgeAt) Or IsNull(varAgeAt) Then varAgeAt = VBA.Date

    If Not IsNull(varDoB) Then

        intYears = DateDiff("yyyy", varDoB, varAgeAt) - _

            IIf(Format(varAgeAt, "mmdd") < Format(varDoB, "mmdd"), 1, 0)

        intMonths = DateDiff("m", varDoB, varAgeAt) - (intYears * 12)

        If Day(varDoB) > Day(varAgeAt) Then

            intMonths = intMonths - 1

        End If

        GetAgeYearsMonths = intYears & ":" & Format(intMonths, "00")

    End If

End Function

In both cases they default to returning the age as of today for the date of birth entered.  To return someone's age on a specific date, pass the date into the function as the optional second argument.  So you'd use:

=GetAge([dob],[arrdate])

Answer 2

Hi Greg

VBA code goes in a module, not a macro.

Create a new module, and add the line:

Option Explicit

at the top (if it isn't there already).

Then paste Ken's GetAge function underneath.

Save the module with a name something like mdlCode (it must NOT be the same as the name of any procedure in your database).

In your query, use the function in an expression for a calculated field:

ArrAge: GetAge( [dob], [arrdate] )

Best wishes,

Graham

Answer 3

Never wrote a function before and would not know how to reference it in a query though.  Do you paste that into a macro window? and how do you reference that code in a query.  Don't really need age in years:months.  Just age in years is fine.  I am looking for Minors vs Adults. I currently have that little blurb in the expression row of a calculated field called age.  Obviously I am not adding your code there, is that VBA script?

Thanks for your patience

Answer 4

You'll find several, more accurate, ways to calculate age here:

http://access.mvps.org/access/datetime/date0001.htm

Answer 5

You could use

=Int(([arrdate]-[dob])/365.25)