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
|
|||
|
|||
Framed Option Group / Nested iif Question
Hi All,
I have created a framed option group called fra_cost in my form called frm_search_criteria. In the option group there are three toggle buttons labelled , and =. In the Option Group Wizard I assigned the value of 3 to , 2 to and 1 to =. Now I want to take the returned value from fra_cost and apply it to an nested iif statement. I've tried numerous statements including: Like IIf([forms]![frm_Search_Criteria]![fra_cost]="",[forms]![frm_search_criteria]![txt_cost], IIf([forms]![frm_Search_Criteria]![fra_cost]="",[forms]![frm_search_criteria]![txt_cost], IIf([forms]![frm_Search_Criteria]![fra_cost]="=",=[forms]![frm_search_criteria]![txt_cost],"*"))) The above statement returns all the rows in my table (it should return two rows). Like IIf([forms]![frm_Search_Criteria]![fra_cost]="3",[forms]![frm_search_criteria]![txt_cost], IIf([forms]![frm_Search_Criteria]![fra_cost]="2",[forms]![frm_search_criteria]![txt_cost], IIf([forms]![frm_Search_Criteria]![fra_cost]="1",=[forms]![frm_search_criteria]![txt_cost],"*"))) The above statement doesn't return any rows. Like IIf([forms]![frm_Search_Criteria]![fra_cost]=3,[forms]![frm_search_criteria]![txt_cost], IIf([forms]![frm_Search_Criteria]![fra_cost]=2,[forms]![frm_search_criteria]![txt_cost], IIf([forms]![frm_Search_Criteria]![fra_cost]=1,=[forms]![frm_search_criteria]![txt_cost],"*"))) The above statement doesn't return any rows. Maybe I've got the nested iif statement all wrong. Obviously, I don't understand what kind of value is returned from my framed option group ("", "", "=" or "3", "2", "1" or 3, 2, 1)??? I would like to take the returned value from fra_cost and apply it as a logical operand in a statement that would read something like: If fra_cost is found to be "less than", then find the records where the value in the cost column is "less than" the value for txt_cost (a number also entered in the frm_Search_Criteria form). To make a long story short, is my problem in the way I've nested the iif statements or because I referred to the fra_cost value incorrectly in the statement? I'd welcome any other thoughts that may come to mind. Thanks so much!! |
#2
|
|||
|
|||
Framed Option Group / Nested iif Question
Flora M wrote:
I have created a framed option group called fra_cost in my form called frm_search_criteria. In the option group there are three toggle buttons labelled , and =. In the Option Group Wizard I assigned the value of 3 to , 2 to and 1 to =. Now I want to take the returned value from fra_cost and apply it to an nested iif statement. I've tried numerous statements including: [snip] Like IIf([forms]![frm_Search_Criteria]![fra_cost]="3",[forms]![frm_search_criteria]![txt_cost], IIf([forms]![frm_Search_Criteria]![fra_cost]="2",[forms]![frm_search_criteria]![txt_cost], IIf([forms]![frm_Search_Criteria]![fra_cost]="1",=[forms]![frm_search_criteria]![txt_cost],"*"))) The above statement doesn't return any rows. [snip] I would like to take the returned value from fra_cost and apply it as a logical operand in a statement that would read something like: If fra_cost is found to be "less than", then find the records where the value in the cost column is "less than" the value for txt_cost (a number also entered in the frm_Search_Criteria form). The key thing you are missing is that you need to have complete exoressions in the result so you can not do this as a criteria for a field. Instead, use a calculated field with its SHow box unchecked. The calculated field's expression could be: IIf([forms]![frm_Search_Criteria]![fra_cost]="3",thefield[forms]![frm_search_criteria]![txt_cost], IIf([forms]![frm_Search_Criteria]![fra_cost]="2",thefield[forms]![frm_search_criteria]![txt_cost], [forms]![frm_Search_Criteria]![fra_cost]="1",thefield=[forms]![frm_search_criteria]![txt_cost]) Or, somewhat shorter: Choose([forms]![frm_Search_Criteria]![fra_cost], thefield=[forms]![frm_search_criteria]![txt_cost], thefield[forms]![frm_search_criteria]![txt_cost], thefield[forms]![frm_search_criteria]![txt_cost]) -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Framed Option Group / Nested iif Question
OK - that makes a lot of sense. I read up on the CHOOSE function and I
feel very clear about that but I'm still not sure how to apply it. I've been working on it but I am having some issues. You suggested the following: Choose([forms]![frm_Search_Criteria]![fra_cost], thefield=[forms]![frm_search_criteria]![txt_cost], thefield[forms]![frm_search_criteria]![txt_cost], thefield[forms]![frm_search_criteria]![txt_cost]) Does "thefield" refer to the column we are searching in the table? Or does "thefield" refer to the expression we are creating in this query? Also, how do I complete the query? In addition to the expression on the field line, do I need to specify other criteria or other information. I feel very close to making it work . Thanks Marsh Marshall Barton wrote: Flora M wrote: I have created a framed option group called fra_cost in my form called frm_search_criteria. In the option group there are three toggle buttons labelled , and =. In the Option Group Wizard I assigned the value of 3 to , 2 to and 1 to =. Now I want to take the returned value from fra_cost and apply it to an nested iif statement. I've tried numerous statements including: [snip] Like IIf([forms]![frm_Search_Criteria]![fra_cost]="3",[forms]![frm_search_criteria]![txt_cost], IIf([forms]![frm_Search_Criteria]![fra_cost]="2",[forms]![frm_search_criteria]![txt_cost], IIf([forms]![frm_Search_Criteria]![fra_cost]="1",=[forms]![frm_search_criteria]![txt_cost],"*"))) The above statement doesn't return any rows. [snip] I would like to take the returned value from fra_cost and apply it as a logical operand in a statement that would read something like: If fra_cost is found to be "less than", then find the records where the value in the cost column is "less than" the value for txt_cost (a number also entered in the frm_Search_Criteria form). The key thing you are missing is that you need to have complete exoressions in the result so you can not do this as a criteria for a field. Instead, use a calculated field with its SHow box unchecked. The calculated field's expression could be: IIf([forms]![frm_Search_Criteria]![fra_cost]="3",thefield[forms]![frm_search_criteria]![txt_cost], IIf([forms]![frm_Search_Criteria]![fra_cost]="2",thefield[forms]![frm_search_criteria]![txt_cost], [forms]![frm_Search_Criteria]![fra_cost]="1",thefield=[forms]![frm_search_criteria]![txt_cost]) Or, somewhat shorter: Choose([forms]![frm_Search_Criteria]![fra_cost], thefield=[forms]![frm_search_criteria]![txt_cost], thefield[forms]![frm_search_criteria]![txt_cost], thefield[forms]![frm_search_criteria]![txt_cost]) -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Framed Option Group / Nested iif Question
"thefield" needs to be repaced by the name of the
field/column in the table that you are searching. The expression needs to be entered in the WHERE Clause in the query's SQL view. If you must work in the query design grid, then put the expression in an empty query Field and set its Criteria to True. Be sure to uncheck the Show box. -- Marsh MVP [MS Access] Flora M wrote: OK - that makes a lot of sense. I read up on the CHOOSE function and I feel very clear about that but I'm still not sure how to apply it. I've been working on it but I am having some issues. You suggested the following: Choose([forms]![frm_Search_Criteria]![fra_cost], thefield=[forms]![frm_search_criteria]![txt_cost], thefield[forms]![frm_search_criteria]![txt_cost], thefield[forms]![frm_search_criteria]![txt_cost]) Does "thefield" refer to the column we are searching in the table? Or does "thefield" refer to the expression we are creating in this query? Also, how do I complete the query? In addition to the expression on the field line, do I need to specify other criteria or other information. I feel very close to making it work . Thanks Marsh Marshall Barton wrote: Flora M wrote: I have created a framed option group called fra_cost in my form called frm_search_criteria. In the option group there are three toggle buttons labelled , and =. In the Option Group Wizard I assigned the value of 3 to , 2 to and 1 to =. Now I want to take the returned value from fra_cost and apply it to an nested iif statement. I've tried numerous statements including: [snip] Like IIf([forms]![frm_Search_Criteria]![fra_cost]="3",[forms]![frm_search_criteria]![txt_cost], IIf([forms]![frm_Search_Criteria]![fra_cost]="2",[forms]![frm_search_criteria]![txt_cost], IIf([forms]![frm_Search_Criteria]![fra_cost]="1",=[forms]![frm_search_criteria]![txt_cost],"*"))) The above statement doesn't return any rows. [snip] I would like to take the returned value from fra_cost and apply it as a logical operand in a statement that would read something like: If fra_cost is found to be "less than", then find the records where the value in the cost column is "less than" the value for txt_cost (a number also entered in the frm_Search_Criteria form). The key thing you are missing is that you need to have complete exoressions in the result so you can not do this as a criteria for a field. Instead, use a calculated field with its SHow box unchecked. The calculated field's expression could be: IIf([forms]![frm_Search_Criteria]![fra_cost]="3",thefield[forms]![frm_search_criteria]![txt_cost], IIf([forms]![frm_Search_Criteria]![fra_cost]="2",thefield[forms]![frm_search_criteria]![txt_cost], [forms]![frm_Search_Criteria]![fra_cost]="1",thefield=[forms]![frm_search_criteria]![txt_cost]) Or, somewhat shorter: Choose([forms]![frm_Search_Criteria]![fra_cost], thefield=[forms]![frm_search_criteria]![txt_cost], thefield[forms]![frm_search_criteria]![txt_cost], thefield[forms]![frm_search_criteria]![txt_cost]) |
Thread Tools | |
Display Modes | |
|
|