Calculating Number of Days/Null Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

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


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

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

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

John W. Vinson [MVP]
 
John:

Thanks for the help. It works.

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


John W. Vinson said:
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?
Click to expand...

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

John W. Vinson [MVP]
Click to expand...
 
ask him... I deleted the message and don't recall what he recommended.

John W. Vinson [MVP]
Click to expand...

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