Criteria added to Query - Nothing showing

[eabtx]

I have a select query built that compares a volume from table 1 and a volume from table 2 and then subtracts one from the other to find the difference. I am trying to limit the difference to anything > 500 but when I add this criteria to the query it returns nothing. I know for a fact there are about 20 records that should appear in this criteria. The SQL code is listed below. Can someone help me with this? When I add the criteria >500 and run the query a parameter box for BookVol is popping up now. It does not do this without the criteria.



Thanks,
Erich


SELECT Table1.PropCode, Table1.PropName, Table2.PropCode, Table2.PropName, Table1.ProdDate, Table1.Product, Table1.AccrualVol, Abs([BD46BQuantity]) AS BookVol, ([BookVol]-[AccrualVol]) AS VolDiff, Table1.AccrualVal, Abs([BD46BAmount]) AS BookVal, [BookVal]-[AccrualVal] AS ValDiff

FROM Table2 INNER JOIN Table1 ON (Table2.PropCode = Table1.PropertyCode) AND (Table2.ProdCode = Table1.Product) AND (Table2.BD46BPeriod = Table1.ProdDate);

WHERE (((([BookVol]-[AccrualVol]))>500));

[Victor EGBE]

Try nesting the query.

Something like this

SELECT * FROM [SELECT Table1.PropCode, Table1.PropName, Table2.PropCode, Table2.PropName, Table1.ProdDate, Table1.Product, Table1.AccrualVol, Abs([BD46BQuantity]) AS BookVol, ([BookVol]-[AccrualVol]) AS VolDiff, Table1.AccrualVal, Abs([BD46BAmount]) AS BookVal, [BookVal]-[AccrualVal] AS ValDiff FROM Table2 INNER JOIN Table1 ON (Table2.PropCode = Table1.PropertyCode) AND (Table2.ProdCode = Table1.Product) AND (Table2.BD46BPeriod = Table1.ProdDate)] AS virtualTable1

WHERE (BookVol-AccrualVol)>500;