Nested IIf Dilemma

[TigerNoire]

Happy Almost New Year!

What I have:

tblVacation Hours Granted

VacPurchased    VacCarryOver    VacMarch    VacJune    Case
24              120             40          40         1
12               60             30          30         2
0                80             20          20         3 
8               120             50          50         4

VacMarch hours are granted at the beginning of April. VacJune hours are granted at the beginning of July. They are unable to be used until then.

I have a date field coded to the current date in my query with a Datepart function pulling the month from the date.
________________________________
What I need:

When the month is less than four, VacCarryOver and VacPurchased equal the available hours. (In case one, available hours would be 144; case 2 would be 72, etc.)
When the month is greater than or equal to 4 but less than 7, VacCarryOver, VacPurchased, and VacMarch equal the available hours. (In case one, available hours would be 184, case 2 would be 102, etc.) When the month is greater than or equal to 7, VacCarryOver, VacPurchased, VacMarch, and VacJune are all totalled together. (In case one, available hours would be 224, case 2 would be 132
etc.)
_________________
What I have tried:

VacAvail: IIf(([Month]<4),IIf([Month]>=4 And [Month]<7,([VacCarryOver]+[VacPurchased]+[VacMarch]),([VacCarryOver]+[VacPurchased])),([VacCarryOver]+[VacPurchased]+[VacMarch]+[VacJune]))

This only works for months less than 4. When the month is equal to 4 or more, VacCarryOver, VacPurchased, VacMarch, and VacJune are all added together... which is incorrect. Where is my mistake or what am I missing? Another pair of eyes is much appreciated!

Thanks,
Val

 

[Golom]

The TRUE condition of the second IIF will never be executed because the condition boils down to

   IIF([Month] < 4,
      IIF ( [Month] >= 4 ...

The second IIF is processed only when [Month] < 4, but the >= 4 condition can never be TRUE when [Month] < 4. Try something like

   [VacCarryOver]+[VacPurchased] + 

   IIf ( [Month] < 4, 0 ,

       IIF ( [Month] < 7, , [VacMarch], [VacMarch]+[VacJune]))

 

[Golom]

Sorry about that ... remove the extra comma after < 7

 

[TigerNoire]

Works beautifully! And simple enough to change Thank you!