nested IIF

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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


Michel Walsh said:
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


babs said:
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
Click to expand...
Click to expand...
 
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





babs said:
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


Michel Walsh said:
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


babs said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pit may be null- getting can't have zero length string error 2
msimatch error 13- someone posted a question as an answer - repost 10
Iif not working for multiple options 10
Nested Conditional Statements 2
Nested Conditional Statement Using Nulls 4
System Resource Exceeded/Query Too Complex 4
Nested IIf statement use 4
Pulling data from values selected in dropdown list-values back to 1

Back
Top