Is there a easy way to list multiple items in a IIF statement that can produce a true or false result?
Here's the background:
I am trying to make an Access query to an Oracle 8i database. Most of what I want to do works. However, one thing I need to do in the query is to populate a new field (Type of Food) based on the results from a field (Food Item) in the query.
In my query I retrieve data from a field call "Food Item". If this field returns 'Orange" then the new field "Type of Food" should populate with the word "fruit". I need this to continue with other other types of food (noodles = Pasta, hambuger = meat, etc.) and finally end with no match = "General Produce"
I've learned that the case statment doesn't work. I have to use the IIF statement. I can get it to work. However, there are lots of food items I have that would equal fruit and so on.
It looks like I'll already have nested IIF statements just to list the catagories. I'd hate to make it even more complex by having more statements for each item itself.
IF there is a better way then using IIF, I'd like to hear that as well.