Using a field reference in a query critereon

[rmccafferty]

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?

 

[DCrake]

Will the variance be the same for each record in the query or will a different variance apply for different records?

David

 

[Atomic Shrimp]

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?

Probably the easiest way would be to use DLookup to pull the value out of the constants table, so it would be something like:

> DLookup("Variance","Constants","[some criterion that identifies the row in the constants table]")

 

[Brianwarnock]

Did you remember to include the table constants in your query, no joins to it of course.

Brian