Calculate Age Function

[Lynn_AccessUser]

I have the following function to calculate the age based on the DOB:

Function Age(varDOB As Variant) As Integer
Dim varAge As Variant

If IsNull(varDOB) Then Age = 0: Exit Function

varAge = DateDiff("yyyy", varDOB, Now)
If Date < DateSerial(Year(Now), Month(varDOB), _
Day(varDOB)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)
End Function

The problem is that the age continues to calculate on people who are deceased. If I have a checkbox to check if the person is deceased, how do I prevent the age from continuing to calculate.

Currently, the age function is being called in the query as
Age: Age([DOB])

 

[SodaJim]

Slight similarities...

Hello,

I don't want to switch this topic but thought I could get some quick help on a related issue pertaining to calculating ages...

I've searched and found solutions for calculating ages but wondered why I receive an error (function entered can't be used in expression) for placing the following code in the control source for a textbox on a form...?

=DateDiff("yyyy",[BirthDate],Now())+Int(Format(Now(),"mmdd")<Format([BirthDate],"mmdd"))

I even have tried this:

=IIf(IsDate([BirthDate]),DateDiff("yyyy",[BirthDate],Date())+(Date()<DateSerial(Year(Date()),Month([BirthDate]),Day([BirthDate]))),Null)

I've also attempted to call a calculate age function from the control source to only receive the same error message

Any direction appreciated,
Jim

 

[kbrooks]

I am by far not an expert, but I'm going to take a stab at the first question. Instead of a check box for the deceased field, can you have a deceased date? Then you can calculate deceased date - date of birth, and if the deceased date is blank...calculate age by today's date - date of birth.

Does that help? Just a guess.