nested IIF

[Guest]

I have a nested if I am having problems with. If Cartage rate is 0 see 1st
part of below. (I copy and pasted each part from below so can understand
better)

Round(IIf([CartageRate]=0,Round([FuelperRate]*[GrossWeight],2),


If the Alternate Cartage Rate field is 0 then do the iif where if Grosswt=0
( see second part of below),

IIF([AltCartageRate]=0,(IIf([GrossWeight]=0,[CartageRate]*[TruckHours],[CartageRate]*[GrossWeight])))),(

If Alt cartage rate is not 0 than do IIF from 3rd part below

IIF([GrossWeight]=0,[AltCartageRate]*[TruckHours],[AltCartageRate]*[GrossWeight])),2)

Don't know how to nest them all and tie together under Cartage Paid with
correct IIF and ()

CartagePaid:
Round(IIf([CartageRate]=0,Round([FuelperRate]*[GrossWeight],2),IIF([AltCartageRate]=0,(IIf([GrossWeight]=0,[CartageRate]*[TruckHours],[CartageRate]*[GrossWeight])))),(IIF([GrossWeight]=0,[AltCartageRate]*[TruckHours],[AltCartageRate]*[GrossWeight])),2)

thanks,
Barb

 

[Michel Walsh]

Hi,


If possible, use a SWITCH( ) rather than a complex sequence of iif.


SWITCH( condition1, return1, condition2, return2, condition3, return3,
true, returnElse)



would return return1, if condition1 is true, ELSE, will return return2 if
condition 2 is true, ... and since true is true, if nothing else is returned
yet, the expression returns returnElse.


It is easier to debug and to maintain, in the long run, than embedded iif.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Below is the code I put in with the switch. Don't know if there is a problem
with the and in the condition. It is saying:

Expression you entered contains the wrong number of arguments.

CartagePaid:
Round(SWITCH([CartageRate]=0,Round([FuelperRate]*[GrossWeight],2),[AltCartageRate]=0
and [GrossWeight]=0,[CartageRate]*[TruckHours],[AltCartageRate=0] and
[GrossWeight]<>0,[CartageRate]*[GrossWeight]),[AltCartageRate<>0 and
[GrossWeight]=0,[AltCartageRate]*[TruckHours],[AltCartageRate<>0 and
[GrossWeight]<>0,[AltCartageRate]*[GrossWeight]),0),2)


Didn't really need the returnElse condition so I just put the 0 would rather
do without if I can.

Please help,
thanks,
Barb

Hi,


If possible, use a SWITCH( ) rather than a complex sequence of iif.


SWITCH( condition1, return1, condition2, return2, condition3, return3,
true, returnElse)



would return return1, if condition1 is true, ELSE, will return return2 if
condition 2 is true, ... and since true is true, if nothing else is returned
yet, the expression returns returnElse.


It is easier to debug and to maintain, in the long run, than embedded iif.



Hoping it may help,
Vanderghast, Access MVP

I have a nested if I am having problems with. If Cartage rate is 0 see 1st
part of below. (I copy and pasted each part from below so can understand
better)

Round(IIf([CartageRate]=0,Round([FuelperRate]*[GrossWeight],2),


If the Alternate Cartage Rate field is 0 then do the iif where if
Grosswt=0
( see second part of below),

IIF([AltCartageRate]=0,(IIf([GrossWeight]=0,[CartageRate]*[TruckHours],[CartageRate]*[GrossWeight])))),(

If Alt cartage rate is not 0 than do IIF from 3rd part below

IIF([GrossWeight]=0,[AltCartageRate]*[TruckHours],[AltCartageRate]*[GrossWeight])),2)

Don't know how to nest them all and tie together under Cartage Paid with
correct IIF and ()

CartagePaid:
Round(IIf([CartageRate]=0,Round([FuelperRate]*[GrossWeight],2),IIF([AltCartageRate]=0,(IIf([GrossWeight]=0,[CartageRate]*[TruckHours],[CartageRate]*[GrossWeight])))),(IIF([GrossWeight]=0,[AltCartageRate]*[TruckHours],[AltCartageRate]*[GrossWeight])),2)

thanks,
Barb

 

[Michel Walsh]

Hi,



if this is your real typing, there are many errors, such as



[AltCartageRate=0]


which should probably be

[AltCartageRate]=0



and further on, you open a [ without closing it.



You number of parentheses is wrong, there is two more ) than there are (


If I remove the starting Round( and its second argument ,2), I am left
with:


SWITCH([CartageRate]=0,Round([FuelperRate]*[GrossWeight],2),
[AltCartageRate]=0 and [GrossWeight]=0,[CartageRate]*[TruckHours],
[AltCartageRate=0] and [GrossWeight]<>0,
[CartageRate]*[GrossWeight]

),
[AltCartageRate<>0 and [GrossWeight]=0,
[AltCartageRate]*[TruckHours],
[AltCartageRate<>0 and
[GrossWeight]<>0,[AltCartageRate]*[GrossWeight])

, 0)


what the , 0 does, hanging there. Note that I didn't make any correction
about the [ ] in the above.



Try:


SWITCH( CartageRate=0, FuelperRate*GrossWeight,
(AltCartageRate=0) and (GrossWeight=0) , CartageRate*
TruckHours,
(AltCartageRate=0) and (GrossWeight<>0),
CartageRate*GrossWeight,
(AltCartageRate<>0) and (GrossWeight=0),
AltCartageRate*TruckHours,
(AltCartageRate<>0) and (GrossWeight<>0),
AltCartageRate*GrossWeight)




Don't use [ ] unless you have illegal names, they obscure the statement
more than anything else, otherwise.



Hoping it may help,
Vanderghast, Access MVP

Below is the code I put in with the switch. Don't know if there is a
problem
with the and in the condition. It is saying:

Expression you entered contains the wrong number of arguments.

CartagePaid:
Round(SWITCH([CartageRate]=0,Round([FuelperRate]*[GrossWeight],2),[AltCartageRate]=0
and [GrossWeight]=0,[CartageRate]*[TruckHours],[AltCartageRate=0] and
[GrossWeight]<>0,[CartageRate]*[GrossWeight]),[AltCartageRate<>0 and
[GrossWeight]=0,[AltCartageRate]*[TruckHours],[AltCartageRate<>0 and
[GrossWeight]<>0,[AltCartageRate]*[GrossWeight]),0),2)


Didn't really need the returnElse condition so I just put the 0 would
rather
do without if I can.

Please help,
thanks,
Barb

Hi,


If possible, use a SWITCH( ) rather than a complex sequence of iif.


SWITCH( condition1, return1, condition2, return2, condition3,
return3,
true, returnElse)



would return return1, if condition1 is true, ELSE, will return return2
if
condition 2 is true, ... and since true is true, if nothing else is
returned
yet, the expression returns returnElse.


It is easier to debug and to maintain, in the long run, than embedded
iif.



Hoping it may help,
Vanderghast, Access MVP

I have a nested if I am having problems with. If Cartage rate is 0 see
1st
part of below. (I copy and pasted each part from below so can
understand
better)

Round(IIf([CartageRate]=0,Round([FuelperRate]*[GrossWeight],2),


If the Alternate Cartage Rate field is 0 then do the iif where if
Grosswt=0
( see second part of below),

IIF([AltCartageRate]=0,(IIf([GrossWeight]=0,[CartageRate]*[TruckHours],[CartageRate]*[GrossWeight])))),(

If Alt cartage rate is not 0 than do IIF from 3rd part below

IIF([GrossWeight]=0,[AltCartageRate]*[TruckHours],[AltCartageRate]*[GrossWeight])),2)

Don't know how to nest them all and tie together under Cartage Paid
with
correct IIF and ()

CartagePaid:
Round(IIf([CartageRate]=0,Round([FuelperRate]*[GrossWeight],2),IIF([AltCartageRate]=0,(IIf([GrossWeight]=0,[CartageRate]*[TruckHours],[CartageRate]*[GrossWeight])))),(IIF([GrossWeight]=0,[AltCartageRate]*[TruckHours],[AltCartageRate]*[GrossWeight])),2)

thanks,
Barb