Age Calculation with 2 different dates

[antmorano]

Hello All:

I am relatively new at creating databases and I have hit a snag
creating a database for retirees. One of the fields that I have on my
form is called "Retiree Age" I figured out how to get Access to only
calculate the age up to the DOD for the retiree. However, not every
retiree has a DOD. If this is the case then I need the age to be
calculated at now. I am figuring that that is a complex if statement
but I haven't figured out how to word it correctly.

This is what I really want it to do: If Retiree DOD=Null then
subtract Retiree DOD from Now, but if Retiree DOD="mm/dd/yyyy"
subtract Retiree DOB from Retiree DOD.

Any input would be greatly appreciated. I am also looking into doing
it in a VBA code, but can't figure that out either.

 

[Carl Rapson]

Hello All:

I am relatively new at creating databases and I have hit a snag
creating a database for retirees. One of the fields that I have on my
form is called "Retiree Age" I figured out how to get Access to only
calculate the age up to the DOD for the retiree. However, not every
retiree has a DOD. If this is the case then I need the age to be
calculated at now. I am figuring that that is a complex if statement
but I haven't figured out how to word it correctly.

This is what I really want it to do: If Retiree DOD=Null then
subtract Retiree DOD from Now, but if Retiree DOD="mm/dd/yyyy"
subtract Retiree DOB from Retiree DOD.

Any input would be greatly appreciated. I am also looking into doing
it in a VBA code, but can't figure that out either.

In the Control Source for a text box you could put something like the
following:

=DateDiff("yyyy", Nz([Retiree DOD], Now), [Retiree DOB])

Carl Rapson

 

[Pat Hartman \(MVP\)]

IIf(IsNull(RetireeDOD), datediff("yyyy", RetireeDOB, date()),
datediff("yyyy", RetireeDOB, RetireeDOD))

If the DOD is null use the current date to calculate the age otherwise use
the DOD.

 

[RDub]

Here is another way to look at this:

If retiree is dead Then
age = date of death - date born
Else
age = now - date born
End If

Then have a look in Access help for the IIF() function

Age = IIF(IsNull([DOD]), datediff("yyyy", [DOB], now()),datediff("yyyy",
[DOB], [DOD]))

However, DateDiff () is not an accurate way to determine a persons age. You
will probably want to roll your own Age function. Google is your friend
here. I got quite a few hits using "vb calculate a person's age" as the
search criteria. The first few pages of hits looked to be relevant.

Ron W