nesting iif on a report

[teiben]

I have a text box which represents the possible plants. So I have number 1, 2, 3, 4 or 5. I want to have iif 1 then Michigan, iif 2 then Georgia, etc. The only way I can get it to work is to create 5 textboxes and put them on top of each other. I'm sure I'm missing something in the help screen.

 

[Travis]

There are a couple ways to solve this:

The best way is to create a Lookup Table and link it with you current data (Thus replacing the passing of 1 2 3 ... with Michigan Georgia ...)

To quickly solve your issue you could also use a private Function. Place this function on the Class Module of the Report (View Code when in the Design view of the Report)

Private Function GetState(ByVal iState as Long) as String

   Select Case iState
     Case 1:  GetState="Michigan"
     Case 2:  GetState="Georgia"
     Case 3: ...
     Case Else:  GetState=iState 'Just in case it is not Defined
   End Select

End Function

Now make the Control Source of a text box : =GetState([The Name of the Field with 1 2 3 ...])