Double Iif Statements

[Joanne]

Hello,
In a query I am trying to enter the number of days a person has taken for a
particular vacation request, and I need to somehow determine 1/2 days, so if
the begin date and end date of the vacation are the same, I am entering .5.
If an employee wants a full day, they will have to enter the end data as the
next day. If the "type of day" is "Personal", "Jury Duty", or "Carryover", I
want Access to enter a "0". I set up a double Iif but if I try to add a
comma and a "0" it tells me I have too many arguments. So I'm not sure how
to proceed. Thank you very much for your help.
______________________________________
TotalVacation: IIf([Type Of Day] In ("Personal","Jury Duty","Carryover") And
[OLP Begin Date]=[OLP End Date],.5,IIf([Type Of Day] In ("Personal","Jury
Duty","Carryover") And [OLP Begin Date]<>[OLP End Date],DateDiff("d",[OLP
Begin Date],[OLP End Date]),0)

 

[Beetle]

IIf([Type Of Day] In("Personal", "Jury Duty", "Carryover"), 0,
IIf(DateDiff("d", [OLP Begin Date], [OLP End Date])=0, .5,
DateDiff("d", [OLP Begin Date], [OLP End Date])))

 

[Joanne]

I tip my hat to you.

IIf([Type Of Day] In("Personal", "Jury Duty", "Carryover"), 0,
IIf(DateDiff("d", [OLP Begin Date], [OLP End Date])=0, .5,
DateDiff("d", [OLP Begin Date], [OLP End Date])))
--
_________

Sean Bailey

Hello,
In a query I am trying to enter the number of days a person has taken for a
particular vacation request, and I need to somehow determine 1/2 days, so if
the begin date and end date of the vacation are the same, I am entering .5.
If an employee wants a full day, they will have to enter the end data as the
next day. If the "type of day" is "Personal", "Jury Duty", or "Carryover", I
want Access to enter a "0". I set up a double Iif but if I try to add a
comma and a "0" it tells me I have too many arguments. So I'm not sure how
to proceed. Thank you very much for your help.
______________________________________
TotalVacation: IIf([Type Of Day] In ("Personal","Jury Duty","Carryover") And
[OLP Begin Date]=[OLP End Date],.5,IIf([Type Of Day] In ("Personal","Jury
Duty","Carryover") And [OLP Begin Date]<>[OLP End Date],DateDiff("d",[OLP
Begin Date],[OLP End Date]),0)

 

[JimBurke via AccessMonster.com]

I think that should be 'Not In' - using 'In' it will set the value to 0 if
Type Of Day is any of those 3 values regardless of the dates. It seems from
your previous example that you want the value to be 0 only if it's not one of
those three.

Some questions, just to make sure you get your desired result. Do the first
and last dates represent the first and last days of the time off, or is the
last one the first day back to work? If it's the first day back to work then
you're OK. If it's the last day of the time off then you would need to add 1
to the datediff value, e.g. if the start/end dates are 1/1/2009 and 1/2/2009,
datediff will give you 1, but that's really 2 days off.

As I said, if the last date is the first day back to work you're OK. But if
not, If the dates are the first/last days of the actual time off, then a
datediff value of 0 won't distinguish between a half day and a full day off.
This leads to another question - are these only date values or are there
times inlcuded as well? If you have a time as well then you can distinguish
between a full day and a half day, otherwise I don't see how you could. Sorry
to bombard you with questions, just trying to make sure your query gives you
exactly what you need.

I tip my hat to you.

IIf([Type Of Day] In("Personal", "Jury Duty", "Carryover"), 0,
IIf(DateDiff("d", [OLP Begin Date], [OLP End Date])=0, .5,

[quoted text clipped - 14 lines]

Duty","Carryover") And [OLP Begin Date]<>[OLP End Date],DateDiff("d",[OLP
Begin Date],[OLP End Date]),0)