Query Calc on newly created fields

[caazchinny]

I have three fields calculating info in my query. The end result I would like to see is the Comp Reqd field to be greater 0. When I put the criteria for this field to >=0, then run the query, the system is asking me to input values for Comp Qty Due, CompInv As Assy, and Comp Inv. I would like to not be prompted when I put the criteria in, as I don't get the prompts when I remove the criteria.

Comp Qty Due: (([orrqty]-[oraqty])*[jtqty])

CompInv As Assy: ([tbl_ipart1.ipqoh]*[jtqty])

Comp Reqd: [Comp Qty Due]-[CompInv As Assy]-[Comp inv]

 

[boblarson]

Someone else may have a more elegant solution, but I have found that if you save that query and then use IT in another one you can use the criteria just fine. That's one way you could do it.

 

[pbaldy]

The problem is that the WHERE clause is evaluated before the SELECT clause, so the aliases are unknown at that time. You can do as Bob suggested or recreate the calculation in the WHERE clause, so instead of

WHERE Alias > 0

You'd have

WHERE A - B > 0

Given the various components of yours, it would be simpler to use the second query solution.