ms-accessiif-function

Iff statement in Access report with possible more than 1 true condition

I want to make a report that could return several conditions. Negative, Positive, Cancelled, Negative/Dilute, or Positive/Dilute. I wrote a IIF statement with several conditions.

=IIf([Negative]="1","NEGATIVE RESULT",
    IIf([Positive]="1","POSITIVE RESULT",
       IIf([Cancelled]="1","CANCELLED TEST",
          IIf([Negative]="1" And [Dilute]="1","NEGATIVE/DILUTE RESULT",
              IIf([Positive]=”1” And [Dilute]=”1”, POSITIVE/DILUTE
              )
          )
       )
    )
)

The first part, the middle, and the end will all work alone as single statements, but it will not work when all together.

What am I doing wrong? Any suggestions?

Solution

  • You could use a mixture of Switch and IIf functions:

    =Switch(
  [Negative]="1", "NEGATIVE" & IIf([Dilute]="1", "/DILUTE", "") & " RESULT",
  [Positive]="1", "POSITIVE" & IIf([Dilute]="1", "/DILUTE", "") & " RESULT",
  [Cancelled]="1", "CANCELLED TEST"
)