Using Conditional Criteria in queries

[Pele]

I wrote a query and I need to put a conditional query in
the criteria pane. Below is an example of the condition
that I'd created but it is not working. I am not confident
that the criteria was written corrctly anyway.

The criteria is such that the values in a particular field
will be limited based on the selections in an Optiongroup
and a Textbox.

I would appreciate any help.

IIf([forms]![4Brand All_form_step0]!
[BrandsortAll_optiongrp]=1,<=(1/([forms]![4Brand
All_form_step0]![TxtBrandListLimit]+0.0005)),<=([forms]!
[4Brand All_form_step0]![TxtBrandListLimit]+0.0005))

 

[Wayne Morgan]

When you look at what you have in the SQL view I think you'll see the problem.

WHERE

.[Field] = IIF([Forms]....=1,

.[Field] <= (1/[Forms]..... + 0.0005),

.[Field] <= ([Forms]..... + 0.0005))

One way around this would be to change it to

WHERE (IIF([Forms]....=1,

.[Field] <= (1/[Forms]..... + 0.0005),

.[Field] <= ([Forms]..... + 0.0005)) = True)

& [Field] would be the table name and field name of the field that you are wanting
to filter.

To get to SQL view, open the query in design view and go to View|SQL View on the menu bar.
After you make the change, go back to design view and see what it did. It should create a
calculated field with True as its criteria, the IIF statement as the field, and the Show
checkbox unchecked.

Another possibility, since in this case you are using <= in both the true and false part,
is to put it on the outside of the IIF.

<=IIF([Forms]....=1,(1/[Forms]..... + 0.0005),([Forms]..... + 0.0005))