Using multiple OR statements in IIF, or nesting

[talktozee2]

Hey, all.

I've tried both of the following methods, but neither is working. Suggestions?

  =IIf([transfer_to]=0,[amount],IIf([transfer_to]=[current_investorID],[amount],IIf([current_typeID]<6,[amount],0)))
=IIf([transfer_to]=0 Or ([transfer_to]=[current_investorID]) Or [current_typeID]<6,[amount],0)

Basically if any of the conditions are true, insert [amount], if not insert 0.

Whenever I add the [current_typeID] option everything breaks. Thanks!

[talktozee2]

I think, because of the relationships of fields/controls, this works:

=IIf(([transfer_to]=0 Or [transfer_to]=[current_investorID]) And [current_typeID]<6,[amount],0)

[Delerna]

I think if you think about the result you wanted and look at the way the three attempts are put together then you will find the answer there.

For example
in 2) you said if any of these 3 things is true then this otherwise this
in 3) you said if either of these 2 things is true AND this is true then this otherwise do this
These are totally different!

Structuring logical equations correctly can be quite tricky sometimes.

[talktozee2]

Delerna,

Once again you are quite right. My 3rd statement did NOT work, but neither does my 2nd statement. Any suggestions? Where am I going wrong?

[Stewart Ross]

...Whenever I add the [current_typeID] option everything breaks...

Hi Talktozee. What do you mean when you say everything breaks?

As Delerna said, your statements 1 and 2 are quite different to statement 3, which as he pointed out was only true if either of the first two conditions AND the third condition were true.

As it is difficult to see the structure of a linear IIF statement I broke down your statement 1 into its logical IF-THEN-ELSE equivalent, then took out the redundant statements and reformed an IIF from the result. It is quite telling...

If [transfer_to]=0 then 
    return [amount]
elseif [transfer_to]=[current_investorID] then
    return [amount]
elseif [current_typeID]<6
    return [amount]
else
   return 0
endif

removing the redundant ELSEIFs by ORing the conditions together

If ([transfer_to]=0) OR ([transfer_to]=[current_investorID]) OR ([current_typeID]<6) then
    return amount
else
    return 0
endif

which in IIF form is just

IIF(([transfer_to]=0) OR ([transfer_to]=[current_investorID]) OR ([current_typeID]<6), 
amount, 0)

identical to your statement 2 that you say does not work. As Delerna pointed out, getting the logic right is difficult, sometimes, and I wonder if you are testing the right types of values, as your statements 1 and 2 are logically equivalent?

-Stewart