IIF in qurey criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to add an IIF statement to my query criteria. I have a form with
an option group (optRept)with 2 choices: 1. Give me all records where total
sales > [Forms]![frmOrderRept].[txtSlsMin] (this a text box on the form to
enter sales $ criteria) or 2. Give me all records where total sales <=
[Forms]![frmOrderRept].[txtSlsMin]. When I run the query I am not getting the
row returned that I should.

The IIF state is as
follows:IIf([Forms]![frmOrderRept].[optRept]=1,([tblSalesSumm].[TotalSls])<Val([Forms]![frmOrderRept].[txtSlsMin]),([tblSalesSumm].[TotalSls])>=Val([Forms]![frmOrderRept].[txtSlsMin]))

When I have option 1 selected I get no rows returned. When I have option 2
selected I get only the records where TotalSales = 0.

To add to my confusion if I eliminate the IIF statement and just use one of
the criteria I get the results I want. Any help would be greatly appreciated.
 
You can't create criteria like that in Access.

If you're comfortable working with the SQL, try setting the WHERE clause to
something like:

WHERE ([tblSalesSumm].[TotalSls]<Val([Forms]![frmOrderRept].[txtSlsMin]) AND
[Forms]![frmOrderRept].[optRept]=1)
OR ([tblSalesSumm].[TotalSls]>=Val([Forms]![frmOrderRept].[txtSlsMin]) AND
[Forms]![frmOrderRept].[optRept]<>1)

Through the graphical query builder, you'll have to have criteria on two
separate lines.
 
Bill Phillips said:
I am trying to add an IIF statement to my query criteria. I have a form
with
an option group (optRept)with 2 choices: 1. Give me all records where
total
sales > [Forms]![frmOrderRept].[txtSlsMin] (this a text box on the form to
enter sales $ criteria) or 2. Give me all records where total sales <=
[Forms]![frmOrderRept].[txtSlsMin]. When I run the query I am not getting
the
row returned that I should.

The IIF state is as
follows:IIf([Forms]![frmOrderRept].[optRept]=1,([tblSalesSumm].[TotalSls])<Val([Forms]![frmOrderRept].[txtSlsMin]),([tblSalesSumm].[TotalSls])>=Val([Forms]![frmOrderRept].[txtSlsMin]))

When I have option 1 selected I get no rows returned. When I have option 2
selected I get only the records where TotalSales = 0.
Click to expand...

It's not at all clear what you think you're trying to do from the above, but
maybe all you need is a bit of clarification on what your IIF statement is
doing. If you don't use the third parameter of an IIF statement, it will
return 0 (false) when the condition in the first parameter is not met. Your
first parameter will also return only 0 or 1, since it is a boolean
evaluation of whether or not the value in one of your controls is greater or
equal to the value of another control. So your Where clause will evaluate
to LIKE 0 or LIKE 1 (You can only use IIF in a LIKE expression).

HTH;

Amy
 
Back
Top