Calculated field with criteria

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have a calculated field which I also add a criteria to. When I run the
query, it throws up a parameter dialog box looking for calculated field. I
tried removing the [] brackets around the calculated field name in the where
clause, but that did not help. Can one use a criteria on a calcuated field?
Thanks
 
Effectively, you need to repeat the entire expression for the calculated
field in the WHERE clause.

The way Access works, it first selects the records, and then calculates the
field. Naturally enough, you cannot place a condition on a field that has
not yet been calculated.
 
If you don't want to repeat the calculation in your where
clause you could save the query without the where clause,
then base a 2nd query on the first, where you could simply
refer to the expression name assigned to the calculation.
good luck.
 
Allen Browne and Elwin - Thank you both for the valuable comments!

My calculated field was a custom function whose parameters were from other
calculated fields. When I rewrote the Where cause using your suggested "you
need to repeat the entire expression for the calculated field in the WHERE
clause," it worked just fine! The idea is that nowhere in the Where clause
should there appear the name of the calculated field - even for arguments to
functions. One should only use the expression which created the calculated
field as you suggested.

Thank you for clarifying that
"The way Access works, it first selects the records, and then calculates the

field. Naturally enough, you cannot place a condition on a field that has

not yet been calculated."


I hope you place this valuable information in the tips section of your
website.

Take care,
John
 
Back
Top