We help IT Professionals succeed at work.

IIF Statement that's getting too complex?

jtflex
jtflex asked
on
Medium Priority
1,266 Views
Last Modified: 2008-01-28
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
Comment
Watch Question

Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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.

Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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

Author

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"Is that possible?"

Sure:

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

mx

Author

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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

Author

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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

Author

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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

Author

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?  
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
""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

Author

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
I'm positive I've had 20 - 25 pairs in the past.  Pretty sure it's got to be something else.

mx

Author

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Don't know anything about Oracle, sorry ...

mx
Top Expert 2007

Commented:
Perhaps if we could actually *see* the SQL...?

Author

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

Author

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

Top Expert 2008

Commented:
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'.
Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
" 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



Commented:
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?
Top Expert 2007

Commented:
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?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"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

Author

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