Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Calculating age with If statement
12 views
Skip to first unread message
SEMarxen
Oct 21, 2009, 4:01:07 PM10/21/09
to
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:
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!
vanderghast
Oct 21, 2009, 4:22:51 PM10/21/09
to
=DateDiff("yyyy",[Birthdate],Nz(Deathdate ,Now()))+Int(Format(Nz(Deathdate
,Now()),"mmdd")<Format([Birthdate],"mmdd"))
,Now()),"mmdd")<Format([Birthdate],"mmdd"))
Vanderghast, Access MVP
"SEMarxen" <SEMa...@discussions.microsoft.com> wrote in message
news:907E5E62-411B-4119...@microsoft.com...
KARL DEWEY
Oct 21, 2009, 4:23:01 PM10/21/09
to
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")))
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")))
--
Build a little, test a little.
0 new messages