Solved

# Automatic Age calculation

Posted on 2005-04-07
1,685 Views
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.

0
Question by:kephillips

LVL 29

Expert Comment

Take a look at the DateDiff function:

Example:

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

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

Leon
0

LVL 44

Expert Comment

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

LVL 3

Expert Comment

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 Comment

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

LVL 44

Expert Comment

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

LVL 29

Expert Comment

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

LVL 44

Expert Comment

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

LVL 3

Accepted Solution

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

## Join & Write a Comment Already a member? Login.

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

#### 734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!