# Automatic Age calculation

Hi,

I have an SQL table which holds data which is displayed on a vb6 form.  One of the input boxes date of birth and the other is age.

How can I get VB to automatically calculate the age and fill the age textbox with the correct number using the date of birth which is entered??

Cheers.

###### Who is Participating?

Commented:
kephillips;  I took the liberty of writing a quick routine for you...

I am assuming that there are two text boxes; txtDOB and txtAGE
_______________________________________
Private Sub txtDOB_Change()
Dim BirthDate As Date
Dim TodayDate As Date
Dim dModifier As Long

If IsDate(txtDOB.Text) Then
BirthDate = CDate(txtDOB.Text)
TodayDate = Now
dModifier = IIf(Format(BirthDate, "MMDD") > _
Format(TodayDate, "MMDD"), -1, 0)
txtAGE.Text = DateDiff("yyyy", BirthDate, TodayDate) + dModifier
End If
End Sub
_______________________________________

Jiggle On ! ! !
0

Commented:
Take a look at the DateDiff function:

Example:

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

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

Leon
0

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

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

Author Commented:
Ok Jiggly, sounds good,

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

0

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

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

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

Leon
0

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