IIF Statement on a Report

[RichB]

I created an unbound field on my report. I want to have it show:

=IIf([Cond_Cap]=1,"FMC",0)
=IIf([Cond_Cap]=2,"PMC",0)
=IIf([Cond_Cap]=3,"NMC",0)

Is there a way to make this one statement. I can't seem to figure it out and help . . . well it wasn't. There are lot's of posts for the IIf statement but none like this that I saw.

Thanks
Rich

 

[Fornatian]

Either use a choose function to return the value in query:

ReturnValue: Choose([Cond_cap],"FMC","PMC",NMC")

or build a lookup table of associated values and join that to your query to return the appropriate string. Dependent on how likely these are to change will govern your decision as the table choice will be easier to update and amend.

 

[RichB]

As usual I need to clarify. What I have is a Report that is reading the information directly from the open form. The information that I need is from an Option Box/toggle buttons. The values behind each button is 1, 2, and 3. The text on the button is FMC, PMC, and NMC. Is there a way (maybe visual Basic) to make the 1, 2, and 3 equal FMC, PMC, and NMC. I do this with an IIF statement for yes/no fields but can not get it for multiple factors.

I hope this clarifies.
Rich

 

[Fizzio]

There is a way with Vb but Fornations second suggestion is better than coding with VBA as you can use this lookup table for a multitude of reports/forms etc if necessary. I'm surprised you do not already have a lookup table with this data in as you will need a reference to what 1,2 and 3 are.

 

[RichB]

Thanks Folks,

I will work with the lookup tables. This is a unique database (da, aren' t they all). Anyway, I am tag teaming it with another person and this is one of those toe stepping issues. Time to put on the steel toed boots.

thanks again
Rich