Automatic Age calculation

Take a look at the DateDiff function:

Example:

DateDiff("yyyy", "11/24/1985", Date)

http://www.w3schools.com/vbscript/func_datediff.asp

Leon

leonstryker,

DateDiff() will only approximate the age by subtracting the year of the birthdate from the current year.  A person's age is a step function, only changing when the month and day (day-of-year) of their birth is less than the month and day (day-of-year) of the current date.

=========================
kephillips,

Do you want to calculate the age in the query as you return the data or in your VB program?

Here is the fastest performing VB/VBA function I've found for correctly calculating age:

Public Function PatientAgeAtEnc(parmBirthDate, parmEncDate) As Integer
   
    Select Case (Month(parmEncDate) * 100 + Day(parmEndDate))
      Case Is < (Month(parmBirthDate) * 100 + Day(parmBirthDate))
          PatientAgeAtScan = DateDiff("yyyy", parmBirthDate, parmEncDate)
      Case Else
          PatientAgeAtScan = DateDiff("yyyy", parmBirthDate, parmEncDate) - 1
    End Select

End Function

In this function, I'm passing both the birthdate and a given date on which we encountered the person.  If you base the age on the given date, you only need one parameter.

I'm pretty sure you can incorporate similar logic in the query.  I invoke this function as part of my MSAccess query.

Directly from the immediate debug window:

____________________________________________

BirthDate = "7/21/1967"
TodayDate = Now
dModifier = IIF(Format(BirthDate, "MMDD") > Format(TodayDate, "MMDD"), -1, 0)

? DateDiff("yyyy", BirthDate, TodayDate) + dModifier
 37
____________________________________________

Jiggle On ! ! !

Ok Jiggly, sounds good,

but where and how do I declare that I want the result of the calculation to be printed in txtAge??

you set the value of txtAge in the property window:
Example:

=DateDiff("yyyy", BirthDate, TodayDate) + IIF(Format(BirthDate, "MMDD") > Format(TodayDate, "MMDD"), -1, 0)

Thanks for the assist aikimark, I was going to point out that you can use year, month, and/or day parameter for DateDiff. :)

Leon

oops...found a typo:

Public Function PatientAgeAtEnc(parmBirthDate, parmEncDate) As Integer
   
    Select Case (Month(parmEncDate) * 100 + Day(parmEncDate))
      Case Is < (Month(parmBirthDate) * 100 + Day(parmBirthDate))
          PatientAgeAtEnc = DateDiff("yyyy", parmBirthDate, parmEncDate)
      Case Else
          PatientAgeAtEnc = DateDiff("yyyy", parmBirthDate, parmEncDate) - 1
    End Select

End Function

==========================
It is possible to simplify the txtAge expression, using the day-of-the-year values:

=DateDiff("yyyy", BirthDate, TodayDate) + IIF(Format(BirthDate, "y") > Format(TodayDate, "y"), -1, 0)

Notes:
* Format() value comparisons are slower than the numeric comparisons I've posted in the PatientAgeAtEnc function above.
* I haven't completely tested the day-of-the-year values.  It is possible they may not give the results I expect in leap years.