donbolinger
MIS
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
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