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
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
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
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.
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.
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
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
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
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
ASKER
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
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
"Is that possible?"
Sure:
Expr1:Switch(IsNull([Food Item]), "YourOtherThanNullValueHer
mx
Sure:
Expr1:Switch(IsNull([Food Item]), "YourOtherThanNullValueHer
mx
ASKER
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
By the way I did test the switch and it's doing just what I need.
Joe
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
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
ASKER
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
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
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
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
ASKER
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
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
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
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
ASKER
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?
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?
""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
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
ASKER
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
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
I'm positive I've had 20 - 25 pairs in the past. Pretty sure it's got to be something else.
mx
mx
ASKER
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
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
Don't know anything about Oracle, sorry ...
mx
mx
Perhaps if we could actually *see* the SQL...?
ASKER
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
"(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
ASKER
Here's the sample:
Switch(FoodItem="Bread","B
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
Switch(FoodItem="Bread","B
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
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'.
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'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
mx - do you really support this approach?
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?
>> 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?
"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
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
ASKER
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
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