• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2019
  • Last Modified:

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.

0
kephillips
Asked:
kephillips
  • 3
  • 2
  • 2
  • +1
1 Solution
 
leonstrykerCommented:
Take a look at the DateDiff function:

Example:

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

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

Leon
0
 
aikimarkCommented:
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
 
JigglyDCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
kephillipsAuthor 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
 
aikimarkCommented:
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
 
leonstrykerCommented:
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
 
aikimarkCommented:
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
 
JigglyDCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now