How to use < and > operators in an IIF statement using form criteria 1

[donbolinger]

Have come to a road block in using criteria gathered from an input form to generate query results and narrowing the results using less than (<) and greater than (>) operators.

I want to allow the user to search for records that have an AVGCOST that is between the two values they enter on the form. Or, they can enter a value in either field and leave the other field blank to return records that are less than or greater than (depending upon which field they populate) the value they enter.

Here is the SQL View of my query (I am trying to get the < field working first before I tackle the > field):


SELECT [Search Criteria Results].MSTRVENNAME, [Search Criteria Results].WHSE, [Search Criteria Results].LINE, [Search Criteria Results].[LB DESC], [Search Criteria Results].KEY, [Search Criteria Results].ALT1, [Search Criteria Results].DESCRIPTION, [Search Criteria Results].[AVG MO DMD], [Search Criteria Results].DEMANDCODE, [Search Criteria Results].RANK, [Search Criteria Results].STATUS, [Search Criteria Results].OBS, [Search Criteria Results].AVGCOST, [Search Criteria Results].[ON HAND], [Search Criteria Results].UM, [Search Criteria Results].[TOTAL $], [Search Criteria Results].MO5, [Search Criteria Results].MO4, [Search Criteria Results].MO3, [Search Criteria Results].MO2, [Search Criteria Results].[LAST MONTH], [Search Criteria Results].[% VARIANCE LAST MONTH], [Search Criteria Results].[AVG DMD (PRIOR 3 MOS)], [Search Criteria Results].[VARIANCE TO AVG], [Search Criteria Results].[% VARIANCE TO AVG], [Search Criteria Results].[CRT BUYER], [Search Criteria Results].SUPERVISOR INTO [Final Query Results]
FROM [Search Criteria Results]

WHERE ((([Search Criteria Results].AVGCOST)=IIf([Forms]![Query Results Form]![Avg]="",[ANALYSIS]![AVGCOST], [ANALYSIS]![AVGCOST]))) HAVING ([ANALYSIS]![AVGCOST]<[Forms]![Query Results Form]![Avg]);


I receive a "Property not found" error when trying to run the query.

Any ideas?

Many thanks,
Don

 

[lespaul]

What is ANALYSIS!AVGCOST?

Leslie

Have you met Hardy Heron?

 

[Golom]

You can't use HAVING without a GROUP BY clause.

Is [ANALYSIS]![AVGCOST] a field in a table? If so, you haven't referenced that table in your FROM clause.

Your IIF doesn't seem to be useful

IIf([Forms]![Query Results Form]![Avg]="",[ANALYSIS]![AVGCOST], [ANALYSIS]![AVGCOST])

It returns the same field for both TRUE and FALSE conditions.

You probably want a WHERE clause like

WHERE AStart IS Null And [ANALYSIS]![AVGCOST] <= AEnd
      OR
      AEnd IS Null And [ANALYSIS]![AVGCOST] >= AStart
      OR
      [ANALYSIS]![AVGCOST] BETWEEN AStart And AEnd

AStart and AEND are the two fields on your form.

 

[jimirvine]

You can't use HAVING without a GROUP BY clause.

Yes you can.
You cannot have a select clause that includes a combination of aggregated an non-aggregated columns without a group by clause, but you can certainly have a having clause without a group by.

 

[dhookom]

I just attempted to create a query without a group by in the Northwind.mdb

SELECT Categories.*
FROM Categories
HAVING CategoryID >3;

I got an error about a HAVING clause with no GROUP BY.

Duane
Hook'D on Access
MS Access MVP

 

[donbolinger]

Now I wish I had brought the database home over the weekend.... I'll try your suggestions Monday and let you know.
Thanks!
Don

 

[Golom]

jimirvine

Yes ... you are right. If you use a having clause without a group by then you must use aggregate functions. For example

Select SUM(x) From myTable HAVING x > 9

does run but

Select x From myTable HAVING x > 9

does not. You get "HAVING clause (x>9) without grouping or aggregation."

 

[donbolinger]

OK - I've incorporated the suggested WHERE clause from Golom's post but now I'd like the query check for two additional criteria fields from the input form - Total and Variance. How would I word the WHERE clause in include these extra two variables? Here is the corrected code that works when it is checking just the Average Cost:

WHERE Forms![Query Results Form]!Avg Is Null And [Search Criteria Results].AVGCOST<=Forms![Query Results Form]!Avggrt 

Or Forms![Query Results Form]!Avggrt Is Null And [Search Criteria Results].AVGCOST>=Forms![Query Results Form]!Avg 

Or [Search Criteria Results].AVGCOST Between Forms![Query Results Form]!Avg And Forms![Query Results Form]!Avggrt;

Many thanks!
Don

 

[Golom]

Don't know exactly what you want to check (i.e. equality, less than, greater than, between, etc.) but the general form will be

WHERE (... the above clause ...)
  AND Total = SomeValue
  AND Variance = SomeOtherValue

You may want to use OR in place of AND.

Be sure to use parentheses to specify the order of evaluation when you have both AND and OR in the same expression.

 

[donbolinger]

I'll play around with the AND and OR to get the right combination.

Thank you all for your help,
Don