Framed Option Group / Nested iif Question

[Flora M]

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!!

 

[Marshall Barton]

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])

 

[Flora M]

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

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])

 

[Marshall Barton]

"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]

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

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])