making two IIf's work together

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

Guest

The statement below will report N/A if VarWComm is less than 0:

AmtOfLoss:
IIf([QryClassActRpt.VarWComm]<=0,Format([QryClassActRpt.VarWComm],"Currency"),"N/A" )

The statement below will report N/A if BuysSellsmatchYN = No:

Expr1:
IIf([QryClassActRpt]![BuysSellsMatchYN]="Yes",[QryClassActRpt]![VarianceWN/A],"N/A")

Any ideas of how I can get these to work together in one query feild?

Robert
 
Dear Robert:

No problem. But, there are two possibilities. There has to be an "outer"
and an "inner" case.

This fact determines what happens if the first test fails but the second
succeeds. What is the correct result when VarWComm < 0 but BuySellsMatchYN
= "Yes"? Is the result N/A or is it VarianceWN/A?

So, there is an "outer" test that is primary. The "inner" test is not made,
or has no effect when the "outer" test succeeds, or fails, depending on how
you construct it.

Here's one possibility (out of 4 total possibilities):

IIf([QryClassActRpt.VarWComm]<=0,Format([QryClassActRpt.VarWComm],"Currency"),
IIf([QryClassActRpt]![BuysSellsMatchYN]="Yes",[QryClassActRpt]![VarianceWN/A],"N/A")
)

This results in the second of your test only mattering when the first test
fails. Reversing that:

IIf([QryClassActRpt]![BuysSellsMatchYN]="Yes",[QryClassActRpt]![VarianceWN/A],
IIf([QryClassActRpt.VarWComm]<=0,Format([QryClassActRpt.VarWComm],"Currency"),"N/A"
)
)

This reverses that.

It is also possible to replace the "true" part of either of the IIf()
functions with the alternate condition. However, it seems most likely you
only want to replace the N/A results with executing the other test. But
which one? I've given both possibilities of that?

Did this help?

Tom Ellison
 
Back
Top