DateDiff Function

[Joanne]

Hello,
I have a form for calculating vacation time. There are 4 text boxes going
horizontally across the form, OLP Begin Date, OLP End Date, Type of Day and
then finally a calculated field (a text box), with the following formula.
=DateDiff("d",[OLP Begin Date],[OLP End Date]). So far this is working well.
But now I would like to add some criteria that the dates should only be
included in the totals field if the "Type of Day" is not "personal" or "Jury
Duty". I added and And clause at the end of the DateDiff function but
clearly that is not the way to go. Is there any way that I can use DateDiff
and use that selection criteria? Does this make sense? Any help would be
greatly appreciated.

 

[Beetle]

=Iif([Type Of Day] = "Personal" Or [Type Of Day] = "Jury Duty", "",
DateDiff("d",[OLP Begin Date],[OLP End Date]))

 

[Joanne]

Thank you so much for your help. This works perfectly. But I'm confused
about something. Wouldn't it be possible in my query to fill in the criteria
field for the calculated field? I'm not sure I understand why that won't
work. I have one field called "TypeOfDay" which is just a drop down of
"Personal", Sick, Jury Duty, etc. Then in the next field, the calculated
field called "TotalDays" I have your Iif statement, but couldn't I put
<>"Personal", <>"Jury Duty", <>"Carryover" in the criteria, telling it to use
the DateDiff Function only if it's not one of those menu picks? Thanks for
your help.

=Iif([Type Of Day] = "Personal" Or [Type Of Day] = "Jury Duty", "",
DateDiff("d",[OLP Begin Date],[OLP End Date]))

--
_________

Sean Bailey

Hello,
I have a form for calculating vacation time. There are 4 text boxes going
horizontally across the form, OLP Begin Date, OLP End Date, Type of Day and
then finally a calculated field (a text box), with the following formula.
=DateDiff("d",[OLP Begin Date],[OLP End Date]). So far this is working well.
But now I would like to add some criteria that the dates should only be
included in the totals field if the "Type of Day" is not "personal" or "Jury
Duty". I added and And clause at the end of the DateDiff function but
clearly that is not the way to go. Is there any way that I can use DateDiff
and use that selection criteria? Does this make sense? Any help would be
greatly appreciated.

 

[Beetle]

You have to evaluate what's in the [TypeOfDay] field *before*
telling Access whether to run the DateDiff function or not.
Basically what the IIf statement says, in plain english, is;

If the Type Of Day field equals "Personal" or "Jury Duty"
then we want nothing ("") in the calculated field/control,
otherwise we want the result of the DateDiff function in the
calculated field/control.

Doing it the way you're suggesting would mean that the DateDiff
function would *always* be run, and the result would then be
compared to whatever you have in the criteria row, which
wouldn't work.

--
_________

Sean Bailey

Thank you so much for your help. This works perfectly. But I'm confused
about something. Wouldn't it be possible in my query to fill in the criteria
field for the calculated field? I'm not sure I understand why that won't
work. I have one field called "TypeOfDay" which is just a drop down of
"Personal", Sick, Jury Duty, etc. Then in the next field, the calculated
field called "TotalDays" I have your Iif statement, but couldn't I put
<>"Personal", <>"Jury Duty", <>"Carryover" in the criteria, telling it to use
the DateDiff Function only if it's not one of those menu picks? Thanks for
your help.

=Iif([Type Of Day] = "Personal" Or [Type Of Day] = "Jury Duty", "",
DateDiff("d",[OLP Begin Date],[OLP End Date]))

--
_________

Sean Bailey

Hello,
I have a form for calculating vacation time. There are 4 text boxes going
horizontally across the form, OLP Begin Date, OLP End Date, Type of Day and
then finally a calculated field (a text box), with the following formula.
=DateDiff("d",[OLP Begin Date],[OLP End Date]). So far this is working well.
But now I would like to add some criteria that the dates should only be
included in the totals field if the "Type of Day" is not "personal" or "Jury
Duty". I added and And clause at the end of the DateDiff function but
clearly that is not the way to go. Is there any way that I can use DateDiff
and use that selection criteria? Does this make sense? Any help would be
greatly appreciated.