Parameter error with query criteia

[LLB]

My ultimate goal was to create a list of projects where the estimate is less than one thousand dollars. I have two different estimate fields to draw the data from. Estimate 2 should be used if it exists and if not, use estimate 1.

My query had 3 calculated expressions:

1)
Estimate1: [Estimate1Capital] + [Estimate1Expense]

2)
Estimate2: [Estimate2Capital] + [Estimate2Expense]

3)
GoveringEstimate: Iif([Estimate2] >0, [Estimate2], [Estimate1]

These calculations work perfectly when run until I add the criteria to filter the GoverningEstimate field to <1000, then I get an "Enter Parameter Value" error for both the Estimate1 and Estimate2 fields.

What am I missing?

Lori

 

[rainman89]

Think you would need 2 queries to do this. since governing expense is an iif , One to calculate the governning expenses and one to pick it if its less than 1000

 

[pbaldy]

If you look at the query in SQL view, I'm guessing you will see the alias names used in the WHERE clause. Because the WHERE clause is evaluated before the SELECT clause, the aliases are unknown. The solution is either to recreate the calculations in the WHERE clause or base a second query on this one.

 

[LLB]

I threw the calc fields onto my main query for my reports and the criteria filter into the sub query within the report and it worked great.

Thanks for the quick help.