Using a field reference in a query critereon

[rjmccafferty1]

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?

 

[dhookom]

Can we assume there is only one record in the Constants table? If so, you can add the table to your query and this will work.

If you need the results to be editable, you can use DLookup()

>DLookup("[Variance]","[Constants]")

Duane
Hook'D on Access
MS Access MVP