Calculating Number of Days/Null Values

[Guest]

I have three fields: AppealDate, MeetingDate, and DecisionDate. MeetingDate
and DecisionDate can have null values because they have not occurred. I want
to create fields that will show the number of days between:

1. AppealDate and MeetingDate.
2. AppealDate and DecisionDate
3. MeetingDate and DecisionDate

I have the following control source for an unbound field named
“FStepATimeLineâ€.

=DateDiff ("d", [AppealDate] , Now())

My problem is how do I get a running value when the fields MeetingDate and
DecisionDate are null? If there is no value for MeetingDate and DecisionDate
I want the value to be current number of days between the AppealDate and now
for #1 and #2 above, and for MeetingDate and now for #3. If a date is
entered for either MeetingDate or DecisionDate I want the values for those
controls to stop. Does this make sense?

Thanks for the help.

 

[OldPro]

I have three fields: AppealDate, MeetingDate, and DecisionDate. MeetingDate
and DecisionDate can have null values because they have not occurred. I want
to create fields that will show the number of days between:

1. AppealDate and MeetingDate.
2. AppealDate and DecisionDate
3. MeetingDate and DecisionDate

I have the following control source for an unbound field named
"FStepATimeLine".

=DateDiff ("d", [AppealDate] , Now())

My problem is how do I get a running value when the fields MeetingDate and
DecisionDate are null? If there is no value for MeetingDate and DecisionDate
I want the value to be current number of days between the AppealDate and now
for #1 and #2 above, and for MeetingDate and now for #3. If a date is
entered for either MeetingDate or DecisionDate I want the values for those
controls to stop. Does this make sense?

Thanks for the help.

You can select which date to use with an isnull( ) function.

if isnull(DecisionDate) and isnull(MeetingDate) then
iDays=DateDiff("d",[AppealDate],Now( ))
elseif isnull(decisionDate) then
iDays=DateDiff("d",[AppealDate],[MeetingDate])
elseif isNull(MeetingDate) then
iDays=DateDiff("d",[AppealDate],[DecisionDate])
else
iDays=DateDiff("d",[MeetingDate],[DecisionDate])
endif

You could replace this with nested iif functions.

 

[Guest]

Thanks for the response. I think I my post was not clear. Lets say that I
have a single unbound control named FStepATimeLine that I want to give me the
number of days between the bound controls AppealDate and MeetingDate, and
MeetingDate may have a null value because no meeting has occurred.

What would I put in the control source of the control FStepATimeLine that
will give me the current number of days between the AppealDate and
MeetingDate if there is a value in MeetingDate and if there is a null value
in MeetingDate, the number of days between the AppealDate and now?

Wouldn't the same control source apply to the other 2 controls I want to add
with modifications? Does this make sense? I do not know where to put your
code.

Thanks for the help.
--
Bob

I have three fields: AppealDate, MeetingDate, and DecisionDate. MeetingDate
and DecisionDate can have null values because they have not occurred. I want
to create fields that will show the number of days between:

1. AppealDate and MeetingDate.
2. AppealDate and DecisionDate
3. MeetingDate and DecisionDate

I have the following control source for an unbound field named
"FStepATimeLine".

=DateDiff ("d", [AppealDate] , Now())

My problem is how do I get a running value when the fields MeetingDate and
DecisionDate are null? If there is no value for MeetingDate and DecisionDate
I want the value to be current number of days between the AppealDate and now
for #1 and #2 above, and for MeetingDate and now for #3. If a date is
entered for either MeetingDate or DecisionDate I want the values for those
controls to stop. Does this make sense?

Thanks for the help.

You can select which date to use with an isnull( ) function.

if isnull(DecisionDate) and isnull(MeetingDate) then
iDays=DateDiff("d",[AppealDate],Now( ))
elseif isnull(decisionDate) then
iDays=DateDiff("d",[AppealDate],[MeetingDate])
elseif isNull(MeetingDate) then
iDays=DateDiff("d",[AppealDate],[DecisionDate])
else
iDays=DateDiff("d",[MeetingDate],[DecisionDate])
endif

You could replace this with nested iif functions.

 

[John W. Vinson]

What would I put in the control source of the control FStepATimeLine that
will give me the current number of days between the AppealDate and
MeetingDate if there is a value in MeetingDate and if there is a null value
in MeetingDate, the number of days between the AppealDate and now?

=DateDiff("d", [AppealDate], NZ([MeetingDate], Date()))

John W. Vinson [MVP]

 

[Guest]

John:

Thanks for the help. It works.

Should the OldPro's code be placed in form's current event?
--
Bob

What would I put in the control source of the control FStepATimeLine that
will give me the current number of days between the AppealDate and
MeetingDate if there is a value in MeetingDate and if there is a null value
in MeetingDate, the number of days between the AppealDate and now?

=DateDiff("d", [AppealDate], NZ([MeetingDate], Date()))

John W. Vinson [MVP]

 

[John W. Vinson]

Should the OldPro's code be placed in form's current event?

ask him... I deleted the message and don't recall what he recommended.

John W. Vinson [MVP]

 

[OldPro]

ask him... I deleted the message and don't recall what he recommended.

John W. Vinson [MVP]

It really depends on when you want the code to run. If you want it to
run everytime someone changes one of these dates, in order to update a
textbox, then put this code in a function that calculates the number
of days and then updates the textbox. Call this function from the
various dates' change events.
P.S. - I like the eloquence of John's code, but he left out the
Decision date.