Age Calculation with 2 different dates

  • Thread starter Thread starter antmorano
  • Start date Start date
A

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.
 
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.
Click to expand...

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
 
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.
 
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
 
Back
Top