Calculating age with If statement

  • Thread starter Thread starter SEMarxen
  • Start date Start date
S

SEMarxen

I'm using Access 2003. I have a calculated field called Age on one of my
forms. It displays the difference between a person's birthdate and now. The
control source for Age is:

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

It works fine as long as the person is still alive. The problem is this
formula keeps aging the person after he dies! In sort of English, I want the
calculated Age to do the following instead:

If Deathdate is null, Age displays the years between Birthdate and
Deathdate, else Age displays the years between Birthdate and now.

It sounds simple, but I haven't figured out how to do it. Any help would be
greatly appreciated!
 
=DateDiff("yyyy",[Birthdate],Nz(Deathdate ,Now()))+Int(Format(Nz(Deathdate
,Now()),"mmdd")<Format([Birthdate],"mmdd"))


Vanderghast, Access MVP
 
Try this --
Age: =IIF([Deathdate] Is Null,
DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd")),
DateDiff("yyyy",[Birthdate],[Deathdate])+Int(Format([Deathdate],"mmdd")<Format([Birthdate],"mmdd")))
 
Back
Top