IIF statement and multiple criteria

[Palmer]

Hi,
I am trying to use an iif statement in my report.
I have a field [field1] and it is has four data points 1,2,3,4

I want to say when [field1]=1,"Statement1" and
when [field1]=2,"Statement2" and when [field1]=3,"Statement3" and when [field1]=4,"Statement4"

Any help on this would be appreciated

Matt

 

[David R]

Nested Iifs are one way to go..

=IIF(Condition1,TruePart,IIF(Condition2,TruePart,IIF(Condition3,TruePart,IIF(Condition4,TruePart,EverythingsFalsePart))))


Each one takes care of the "False" option from the one before it. The last becomes a default if none of them come true.

You could also use a function that uses Switch, which might be faster if you've got 4 or more options. IIFs get a little CPU-heavy after a while.

 

[Palmer]

Switch?

Thanks for the quick reply. I was trying something similar to what you posted, but I was and am getting errors. (I got it to work now. Thanks.)

You mentioned using a switch. Could you give me an example?

Thanks again,

Matt

 

[Rich]

Switch is no more efficient than Iif, both have to evaluate all conditions, use a Select Case statement instead
HTH

 

[Palmer]

Case statements?

Hi,
Thanks for the reply.

What is the proper syntax for a case statement?

Thanks,
Matt

 

[David R]

My apologies; I posted Switch in several cases where I meant Select, last week

Must be bleeding my different languages together.

Anyway...

Dim newval As String
Select Case Field1
  Case 1
     newval = "Statement 1"
  Case 2
     newval = "Statement 2"
  Case 3
     newval = "Statement 3"
  Case 4
     newval = "Statement 4"
End Select

    Me.ThisField = newval