#Error for False

[CJ]

Hi Groupies

This Nested IIF statement returns #Error for the False result. Can anybody
see why? I am missing it...

RegHrs: IIf([Payroll Rule]="8 Daily - 44 Weekly" And [Total Time]<=8,[Total
Time],IIf([Payroll Rule]="10 Daily - 44 Weekly" And [Total Time]<=10,[Total
Time],IIf([Payroll Rule]="10 Daily - 40 Weekly" And [Total Time]<=10,[Total
Time],"")))

 

[John W. Vinson]

Hi Groupies

This Nested IIF statement returns #Error for the False result. Can anybody
see why? I am missing it...

When I have more than a couple of nested IIF's, I'll switch to the Switch
function instead. It takes an arbitrary number of pairs of arguments;
evaluates them left to right; and when it finds a pair with a True first
value, returns the second value:

RegHrs: Switch(
[Payroll Rule]="8 Daily - 44 Weekly" And [Total Time]<=8, [Total Time],
[Payroll Rule]="10 Daily - 44 Weekly" And [Total Time]<=10, [Total Time],
[Payroll Rule]="10 Daily - 40 Weekly" And [Total Time]<=10, [Total Time],
True, Null)


The second and third options could of course be combined into one using OR
logic.

 

[Marshall Barton]

This Nested IIF statement returns #Error for the False result. Can anybody
see why? I am missing it...

RegHrs: IIf([Payroll Rule]="8 Daily - 44 Weekly" And [Total Time]<=8,[Total
Time],IIf([Payroll Rule]="10 Daily - 44 Weekly" And [Total Time]<=10,[Total
Time],IIf([Payroll Rule]="10 Daily - 40 Weekly" And [Total Time]<=10,[Total
Time],"")))

I suspect the error has something to do with the false part
being a string, when all the other parts are not.

See John's reply for how I would recommend doing the whole
thing and nite that he uses Null instead of ""

 

[CJ]

Thanks for the information (and solutions) guys.

I didn't realize that rule applied in IIF statements.