I need to know how to use a table-field reference in the criteria for a query result.

I want to see all records where field 1 is different than field 2 by a certain variance amount. I do NOT want to enter this amount as a number each time the query is run via a parameter query for reasons not relevant to my request for help. I want the variance amount to come from a field in a table.

For the simplified example, lets say that I want to know all instances where amount 1 is more than amount2 by more than 5%.

First I enter .05 in the Variance Field of the Constants table.

Then I want a "column" in the query that is headed:
AmountDiff: ([table1].[amount1]-[table2].[amount2])/[table2].[amount2])

This will get me the different between the two amounts as a percentage of Amount2.

Then in the criteria, I want to enter the value of the Variance field in the Constants table. But if I put
>[Constants].[Variance]
in the criteria row, Access thinks that I am asking for user input.

How to I put that reference in the criteria row?

This happens to me often when I have joins. I create a query with all the parameters, including calculated ones; and when I put a criterion in the calculated field, Access thinks I'm asking for user input. It's quite annoying.

The only way I am able to get around it is to leave that query alone with no criteria. Then I create a second query which pulls in all the fields from the first and I put the criteria in that second query.

__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division