If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
IIf statement in query criteria
I am having a problem with the iif statement when I use it in the criteria
for a query for a combo box (Combo2) that is based on the selection of another combo box (Combo1) Essentially, what I am trying to do is show all choices in combo2 if there has not been a selection in Combo1; And show a filtered selection in Combo2 if there is a selection in Combo1. I am trying to do this using the iif statement when setting the criteria in the Combo2 query. iif (isnull(combo1), No Criteria is Set, Criteria is set) I am having trouble with the "No Criteria is Set" part of the expression. Leaving it blank, inserting Null or "" (zero length string" doesn't work. Why am I doing this? Not only do I want to be able to make a selection in Combo1 then a selection in Combo2 based on Combo1's selection but I want to be able to do it the other way round. ie make a selection in Combo2 (so I need all choices displayed) which will automatically make a selection in Combo1. I hope all this makes sense. Is there a way? Or am I going about this the wrong way. Any help would be greatly appreciated. Thanks |
#2
|
|||
|
|||
IIf statement in query criteria
It would help if you showed the query you are trying to build and told us a
little bit about the type of fields you are applying the criteria against. SELECT Field1 FROM SomeTable WHERE Field2 = Forms![NameOfForm]![NameOfComboSource] If Field2 always has a value and is always text you can use: SELECT Field1 FROM SomeTable WHERE Field2 LIKE Nz(Forms![NameOfForm]![NameOfComboSource],"*") Otherwise you can use SELECT Field1 FROM SomeTable WHERE Field2 = Forms![NameOfForm]![NameOfComboSource] OR Forms![NameOfForm]![NameOfComboSource] Is Null John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Proko wrote: I am having a problem with the iif statement when I use it in the criteria for a query for a combo box (Combo2) that is based on the selection of another combo box (Combo1) Essentially, what I am trying to do is show all choices in combo2 if there has not been a selection in Combo1; And show a filtered selection in Combo2 if there is a selection in Combo1. I am trying to do this using the iif statement when setting the criteria in the Combo2 query. iif (isnull(combo1), No Criteria is Set, Criteria is set) I am having trouble with the "No Criteria is Set" part of the expression. Leaving it blank, inserting Null or "" (zero length string" doesn't work. Why am I doing this? Not only do I want to be able to make a selection in Combo1 then a selection in Combo2 based on Combo1's selection but I want to be able to do it the other way round. ie make a selection in Combo2 (so I need all choices displayed) which will automatically make a selection in Combo1. I hope all this makes sense. Is there a way? Or am I going about this the wrong way. Any help would be greatly appreciated. Thanks |
#3
|
|||
|
|||
IIf statement in query criteria
I am trying to do this using the iif statement when setting the criteria in
the Combo2 query. Try this -- Like iif (isnull([combo1]), "*", [combo1]) but I want to be able to do it the other way round. ie make a selection in Combo2 (so I need all choices displayed) which will automatically make a selection in Combo1. I do not see how making a selection in Combo2 will ever control Combo1. -- Build a little, test a little. "Proko" wrote: I am having a problem with the iif statement when I use it in the criteria for a query for a combo box (Combo2) that is based on the selection of another combo box (Combo1) Essentially, what I am trying to do is show all choices in combo2 if there has not been a selection in Combo1; And show a filtered selection in Combo2 if there is a selection in Combo1. I am trying to do this using the iif statement when setting the criteria in the Combo2 query. iif (isnull(combo1), No Criteria is Set, Criteria is set) I am having trouble with the "No Criteria is Set" part of the expression. Leaving it blank, inserting Null or "" (zero length string" doesn't work. Why am I doing this? Not only do I want to be able to make a selection in Combo1 then a selection in Combo2 based on Combo1's selection but I want to be able to do it the other way round. ie make a selection in Combo2 (so I need all choices displayed) which will automatically make a selection in Combo1. I hope all this makes sense. Is there a way? Or am I going about this the wrong way. Any help would be greatly appreciated. Thanks |
#4
|
|||
|
|||
IIf statement in query criteria
I have done a similiar thing a while back, but no programming expert. Think
I did it all with tables and queries. Do something along the lines of having three tables. table 1 and table 2 for combo1 and table 3 for combo2. Table1 has static data, table 3 and 2 get populated by update queries based on what gets dirty on the form. That way you can update from either combo. This is not exact and doing this from memory but it did work for me , just have a play. I remember that me.refresh came in handy also. -- blindman "Proko" wrote: I am having a problem with the iif statement when I use it in the criteria for a query for a combo box (Combo2) that is based on the selection of another combo box (Combo1) Essentially, what I am trying to do is show all choices in combo2 if there has not been a selection in Combo1; And show a filtered selection in Combo2 if there is a selection in Combo1. I am trying to do this using the iif statement when setting the criteria in the Combo2 query. iif (isnull(combo1), No Criteria is Set, Criteria is set) I am having trouble with the "No Criteria is Set" part of the expression. Leaving it blank, inserting Null or "" (zero length string" doesn't work. Why am I doing this? Not only do I want to be able to make a selection in Combo1 then a selection in Combo2 based on Combo1's selection but I want to be able to do it the other way round. ie make a selection in Combo2 (so I need all choices displayed) which will automatically make a selection in Combo1. I hope all this makes sense. Is there a way? Or am I going about this the wrong way. Any help would be greatly appreciated. Thanks |
#5
|
|||
|
|||
IIf statement in query criteria
Thanks Karl, I'll give it a try when I get to work. Makes sense though! Proko "KARL DEWEY" wrote: I am trying to do this using the iif statement when setting the criteria in the Combo2 query. Try this -- Like iif (isnull([combo1]), "*", [combo1]) but I want to be able to do it the other way round. ie make a selection in Combo2 (so I need all choices displayed) which will automatically make a selection in Combo1. I do not see how making a selection in Combo2 will ever control Combo1. -- Build a little, test a little. "Proko" wrote: I am having a problem with the iif statement when I use it in the criteria for a query for a combo box (Combo2) that is based on the selection of another combo box (Combo1) Essentially, what I am trying to do is show all choices in combo2 if there has not been a selection in Combo1; And show a filtered selection in Combo2 if there is a selection in Combo1. I am trying to do this using the iif statement when setting the criteria in the Combo2 query. iif (isnull(combo1), No Criteria is Set, Criteria is set) I am having trouble with the "No Criteria is Set" part of the expression. Leaving it blank, inserting Null or "" (zero length string" doesn't work. Why am I doing this? Not only do I want to be able to make a selection in Combo1 then a selection in Combo2 based on Combo1's selection but I want to be able to do it the other way round. ie make a selection in Combo2 (so I need all choices displayed) which will automatically make a selection in Combo1. I hope all this makes sense. Is there a way? Or am I going about this the wrong way. Any help would be greatly appreciated. Thanks |
#6
|
|||
|
|||
IIf statement in query criteria
Thanks,
I'll give your idea some thought. Proko "blindman" wrote: I have done a similiar thing a while back, but no programming expert. Think I did it all with tables and queries. Do something along the lines of having three tables. table 1 and table 2 for combo1 and table 3 for combo2. Table1 has static data, table 3 and 2 get populated by update queries based on what gets dirty on the form. That way you can update from either combo. This is not exact and doing this from memory but it did work for me , just have a play. I remember that me.refresh came in handy also. -- blindman "Proko" wrote: I am having a problem with the iif statement when I use it in the criteria for a query for a combo box (Combo2) that is based on the selection of another combo box (Combo1) Essentially, what I am trying to do is show all choices in combo2 if there has not been a selection in Combo1; And show a filtered selection in Combo2 if there is a selection in Combo1. I am trying to do this using the iif statement when setting the criteria in the Combo2 query. iif (isnull(combo1), No Criteria is Set, Criteria is set) I am having trouble with the "No Criteria is Set" part of the expression. Leaving it blank, inserting Null or "" (zero length string" doesn't work. Why am I doing this? Not only do I want to be able to make a selection in Combo1 then a selection in Combo2 based on Combo1's selection but I want to be able to do it the other way round. ie make a selection in Combo2 (so I need all choices displayed) which will automatically make a selection in Combo1. I hope all this makes sense. Is there a way? Or am I going about this the wrong way. Any help would be greatly appreciated. Thanks |
Thread Tools | |
Display Modes | |
|
|