asked on How update ages when a form is opened
I'm trying to figure out how to update the ages of individuals in a table using an update query when a form opens and then update a yes/no checkbox field to yes if the individual is 19 or under and false if the individual is 20 or older. Here is what I have so far but it isn't working:
![UpdateAges]()
Microsoft Access
Last Comment
Dale Fye
and BTW, a better check for the minor field would be >19, False, True
in that it would be a tad faster (one less comparison operation).
Jim.
in that it would be a tad faster (one less comparison operation).
Jim.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Perfect! Thank you.
I don't store ages, unless it is in a temporary table (or maybe a table in a report database where the temp table gets updated once a day and then gets used throughout the day).
I just compute Age on a form, using a controls ControlSource and a function call.
ControlSource: =fnAge([DOB])
I just compute Age on a form, using a controls ControlSource and a function call.
ControlSource: =fnAge([DOB])
Public Function fnAge(DateOfBirth As Variant, Optional TestDate As Variant = Null) As Variant
If IsNull(DateOfBirth) Then
fnAge = Null
Else
If IsNull(TestDate) Then TestDate = Date
fnAge = DateDiff("yyyy", DateOfBirth, TestDate) + (Format(DateOfBirth, "mmdd") > Format(TestDate, "mmdd"))
End If
End Function
Jim.