calculation of age and agecat

[leaf]

Hi there,
I have 3 fields in a Table_age55. When I am in the
form_age55, after I enter date of birth (DOB), I would
like to have age and agecat automatically calculated and
show up in the form_age55 and have values entered into the
table_age55. The following codes were used and they are
not working properly. Please help again.
Thanks,
Leaf

Private Sub DOB_AfterUpdate()
Age = DateDiff("yyyy", DOB, DateofPhoneScreen)
Select Case Age
Case Is < 40
AgeCat = "<40"
Case 40 To 55
AgeCat = "40 to 54"
Case Is >= 55
AgeCat = ">=55"
End Select
End Sub

 

[Douglas J. Steele]

No reason Age needs to be a double: DateDiff returns a Long Integer.

And that approach to calculating age is a tad inaccurate. If you use
DateDiff to give you the difference between, say, 31 Dec, 2002 and 1 Jan,
2003, it's going to say 1 year, even though it's only been a day.

To calculate the age correctly, you need to take into account whether or not
the birthday has already occurred in the year. Here's one way to do that:

Age=DateDiff("yyyy", DOB, Date()) - _
IIf(Format(Date(), "mmdd") < Format(DOB, "mmdd"), 1, 0)