Criteria Filtering Problem

[JackDempsey]

Hello! I'm having problems getting a query to filter based on a criteria. I'm using a two-step querying process(query based on a query), which basically performs some analysis and multiplication after the first query has returned the proper date range of the records.

I have a two fields for comparison in my second query. The first is a Potential QTY field.

PotentialQty: IIf(([Factory_Drawbacks_RPT1]![SumOfbalance_due_qty]-[Factory_Drawbacks_RPT1]![total_onhand_qty])>[Factory_Drawbacks_RPT1]![EXCESS_AVAIL_FOR_MFG],[Factory_Drawbacks_RPT1]![EXCESS_AVAIL_FOR_MFG],([Factory_Drawbacks_RPT1]![SumOfbalance_due_qty]-[Factory_Drawbacks_RPT1]![total_onhand_qty]))

Criteria: >0

The second field:
Dollars: IIf([FS_COST],[FS_COST]*[PotentialQty],[LA_COST]*[PotentialQty])

My problem is that when I enter a criteria of >1000 in the dollars field, or use an inputbox to in the criteria section, the query prompts me again for [PotentialQty]. I've also tried creating a MinDoll column which contains this amount in every row; then doing a >=[MinDoll], to no avail. Sometimes it will filter fine on say 500, but 1000 returns all the records.

When I use the access filter, it works fine, but the query is for a report, and the query doesn't automatically filter on run. I've tried setting filter properties in the Report Properties, and in VB in OnOpen event. Any thoughts?

 

[Cagliostro]

try to use your query as a subquery like source for you report.
select * from (select.....) as t where t.x=... and/or t.y=... John Fill
ivfmd@mail.md