Using a field reference in a query critereon

Aug 9, 2009
Reaction score
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
in the criteria row, Access thinks that I am asking for user input.

How to I put that reference in the criteria row?

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads