IIF Statement that's getting too complex?

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.

Thanks
Joe
jtflexAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LimbeckCommented:
why dont you list the combination type of food/food items in a new table? easier if you want to add new items in the future and if you ever want to change a describtion or anything
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
You can use the Switch Statement:

Switch Function
     

Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.

Syntax

Switch(expr-1, value-1[, expr-2, value-2 & [, expr-n,value-n]])

The Switch function syntax has these parts:

Part Description
expr Required. Variant expression you want to evaluate.
value Required. Value or expression to be returned if the corresponding expression is True.



Remarks

The Switch function argument list consists of pairs of expressions and values. The expressions are evaluated from left to right, and the value associated with the first expression to evaluate to True is returned. If the parts aren't properly paired, a run-time error occurs. For example, if expr-1 is True, Switch returns value-1. If expr-1 is False, but expr-2 is True, Switch returns value-2, and so on.

Switch returns a Null value if:

None of the expressions is True.


The first True expression has a corresponding value that is Null.
Switch evaluates all of the expressions, even though it returns only one of them. For this reason, you should watch for undesirable side effects. For example, if the evaluation of any expression results in a division by zero error, an error occurs.
0
GRayLCommented:
You need a table of Foods and a table of FoodTypes

FoodTypes
========
TypeID - pk
TypeName - text

Foods
====
FoodID - pk
TypeID - foreign key from FoodTypes
FoodName - text

Now all your foods will be able to find the type and goodby IIF()'s
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
The above info is from the Help File, btw.

So, for example:

Expr1:Switch([Food Item] = "Orange",  "fruit", [Food Item] = "Whatever", "SomethingElse" .... and so on)

This could be the Update expression in an Update query.

mx
0
jtflexAuthor Commented:
Thanks for the quick comments from everybody.  
I agree that a new table makes sense.  However, this query is suppose to only retrieve the data.  We don't have the ablilty to create or update the current tables in the database.

I like the Switch function.  Just one question though,  I need a default value other than null for those without a category.  Is that possible?

Joe
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"Is that possible?"

Sure:

Expr1:Switch(IsNull([Food Item]), "YourOtherThanNullValueHere",    [Food Item] = "Orange",  "fruit", [Food Item] = "Whatever", "SomethingElse" .... and so on)

mx
0
jtflexAuthor Commented:
Sorry you misunderstool my question.  There will be items that don't have a category (to include misspellings).  I needed a "catch everthing else" without listing them out final.

By the way I did test the switch and it's doing just what I need.

Joe
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
ok ... Category?  What field name is that?

Note ... you can put other field names in the Switch statement.  It's always
<condition> , <result> .....

So ... not quite sure what you are asking.

mx
0
jtflexAuthor Commented:
Sorry,
What I'm saying is I have 5 major "Food Types" to match up specifically.  The rest I need to just put in a catch all "Food Type" called "General Produce".  

So I'm trying to write it as an If than else.  The else being anything I didn't list previously.  This will be a large number of items and I didn't want to write them in if I didn't have to.

Joe
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, you could still use one IIF in conjunction with the Switch.  

Are you saying that Food Type can be Null?  If so ... then maybe:


Expr1:Switch(IsNull([Type of Food]), "General Produce"",    [Food Item] = "Orange",  "fruit", [Food Item] = "Whatever", "SomethingElse" .... and so on)

mx
0
jtflexAuthor Commented:
No actually what I'm saying is the "Food Item" list which would be used for "General Produce" is so large that instead of listing each item out, I'd list the others in the switch statement first.  Then, anything I didn't list would default to  "General Produce" in the "Type of Food" field.

Thanks for the help.  I'm actually finished with my switch statement and it works great.  This issue is all I have left to resolve.

Joe
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
ok ... well, you kind of see what the Switch stmt can do ... so .....

You could also look at the IN operator ... and/or Not In ( .....)

Or something like

Expr1:Switch([Food Item] = "Orange",  "fruit", [Food Item] = "Whatever", "SomethingElse" .... [Food Item]  <> ("Orange" Or "Apples" or "Avacado" ......)  , "General Procuce" )

where the last expression basically tests for NOT any of the previous cases ... and results in General Produce.  I think THIS is what you mean ?

mx
0
jtflexAuthor Commented:
That might work.

Now I just ran accross a new issue.  It looks like you can only list 14 items (Food Items).  When I added a 15th one I get a "Expression too complex in query expression" error message.

It's a shame as I only had 5 more (Food Items) to list in the switch statement (not counting the ones going to the "General Produce" category).

Any ideas?  
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
""Expression too complex in query expression" "

I'm not aware of any inherent limit on the Switch function.  Be SURE you have matched pairs ... correct field names,  pairs of double quotes, etc.

mx
0
jtflexAuthor Commented:
I've tried retyping the last expression several times using different values also.

Same thing.  I can do 14 entires and run the report.  The 15th entries give the above error message when I try to run it.

Joe
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I'm positive I've had 20 - 25 pairs in the past.  Pretty sure it's got to be something else.

mx
0
jtflexAuthor Commented:
Well I tried a brand new Switch statement and also IIF statement.

I get the same results.  14th expression works.  15th expression doesn't.  I've also replaced some of the expressions with the other unused ones.  Same thing.

If there is no limit for this statement in Access, how about the query going to the Oracle database?  It's oracle 8i and Access is 2003.

Joe

Joe
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Don't know anything about Oracle, sorry ...

mx
0
BadotzCommented:
Perhaps if we could actually *see* the SQL...?
0
jtflexAuthor Commented:
This was in Microsoft's knowledge base.
"(209761) - There is an undocumented limit on the number of ANDs that you can use in an SQL Select statement. ... Microsoft Access 2000 returns ... The error can also occur if the query includes a Switch() function expression that contains more than 13 argument sets. "

It refers to Access 2000 and I'm using 2003.  Could it still be an issue?

Joe
0
jtflexAuthor Commented:
Here's the sample:
Switch(FoodItem="Bread","Bread Product",FoodItem= "Apple","Fruit Product",
FoodItem= "Orange","Fruit Product",FoodItem= "Peach","Fruit Product",
FoodItem= "Pear","Fruit Product",FoodItem= "Banana","Fruit Product",
FoodItem= "Grape","Fruit Product",FoodItem= "Pineapple","Fruit Product",
FoodItem= "Kiwi","Fruit Product",FoodItem= "Mango","Fruit Product",
FoodItem= "Cherry","Fruit Product",FoodItem= "Blueberry","Fruit Product",
FoodItem= "Strawberry","Fruit Product",FoodItem= "Pork","Meat Product",
FoodItem= "Chicken","Meat Product",FoodItem= "Beef","Meat Product",
FoodItem= "Pepsi","Soft Drink Product",FoodItem= "Noodles","Pasta Product",
FoodItem= "Rice","Rice Product") AS FoodTypes

0
ee_rleeCommented:
hi i just read the thread, i'm not sure about the too many expressions problem yet. but there is another way to put a category for those not on the list using switch.

The function SWITCH evaluate a list of expressions and returns the value of the first expression that TRUE is. so you can just put something like 1=1 on the last term and put the value as 'General Produce'.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
" Could it still be an issue?"

"contains more than 13 argument sets. ""

News to me. But you had 14 w/o an error, right ?

How about using the IN operator to reduce the number of pairs and ee_rlee's suggestion of 1=1:

Switch(FoodItem="Bread","Bread Product",FoodItem In( "Apple", "Orange", "Peach", "Pear", "Banana" ,"Grape", "Pineapple", "Kiwi", "Mango", "Cherry", "Blueberry", "Strawberry") ,"Fruit Product", FoodItem IN( "Pork", "Chicken", "Beef") ,"Meat Product", FoodItem= "Pepsi","Soft Drink Product",FoodItem= "Noodles","Pasta Product", FoodItem= "Rice","Rice Product", 1=1, "General Produce") AS FoodTypes

Double check my # of parens, double quotes, etc.

mx



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GRayLCommented:
If you would design the database properly as I said at the beginning and include a table of FoodTypes joined to Foods, you wouldn't have to go thru this hastle.

mx - do you really support this approach?
0
BadotzCommented:
Asker says:

>> I agree that a new table makes sense.  However, this query is suppose to only retrieve the data.  We don't have the ablilty to create or update the current tables in the database.

One wonders, "Why?" Is it because of a restrictive IT policy? Is the DB hosted offsite?

Or is this homework?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"do you really support this approach?"

As I'm sure you have figured out now gRay, I am a HUGE fan of Relational database designs and understand the subject very ... and ALL of my designs *start* with a relational model.  So,  of course, I support the table driven approach.  In fact, almost every aspect of any design I do is table driven ... almost nothing is hard coded.  

However, in the Q's here on EE, I rarely try to get the asker to 'redesign' their database, instead just try to answer the question at hand, and roll with their current approach.  And in this particular case, I've exposed the asker to the Switch function, which is very powerful in many cases, but not necessarily in this case - only because of the many choices, but it loosely follows along with the original IIF approach.

mx
0
jtflexAuthor Commented:
Hi mx and everybody else.
I used mx's solution and it worked perfectly for what we needed.

Just to expain to the others.  This orcale database was opened to us with a view only to retrieve.  It had it's own separate purpose and no, the company hosting it for us would not redesign it for this one small project.  And  I agree with you all, relational databases are the way to go.

Thanks for your time and patience and all the comments from others.

Joe
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.