IFF statement w/5 possible?

[teiben]

Can this be done? I have a field called plant, which stores a F, C, G, B or China. Now I have to create reports, and I have to show what the plant is. (F,C,G,B are unacceptable)
Other than going back in at the table and fixing how I have these values store, I thought I would use an IFF statement.
I've tried a bunch of combinations such as

=IIf([Plant]="C","Chesterfield", iff [Plant]="F" "Fraser", iff [Plant]"B", "Belgium", iff[Plant] = "china", "China" )
I'm getting errors, so I'm not sure if I should be using the iif or if my syntax is wrong. HELP

 

[reclusivemonkey]

Dam, I replied to this once, don't know where it went! Basically your syntax is all over the place. You've use IFF instead of IIF, you've missed "=", and you've missed the FALSE condition from your statement and a couple of brackets. I would suggest you use the "Build" wizard in your query if your syntax isn't good. Try;

IIf([Plant]="C","Chesterfield",IIf([Plant]="F","Fraser",IIf([Plant]="B","Belgium",IIf([Plant]="china","China","No value"))))

Note: you've left G out of the mix; add that if you need it. You can set "No Value" to NULL, or anything else you like.

 

[Rich]

If you'd have created and used a look up table then you wouldn't have needed the multiple Iifs and could have added as many locations as your heart desired without any extra coding