<>0 criteria in query does not work

[Guest]

I created a query that has many calculations. It all works fine until I
enter the criteria "<>0" in one of the calculated columns. It then treats
the columns that are the addends in that equation as if they were parameter
queries. In other words, it asks me to input values for the two columns that
I am adding in that column. If I take it a step back and input the <>0 on
the criteria lines for those two columns that are added together, it than
asks me to input values for the columns that are added together to calculate
those columns. What am I doing wrong!!!!!

HELP!!!!

Thanks!

 

[Wolfgang Kais]

Hello "Pat A".

I created a query that has many calculations. It all works fine
until I enter the criteria "<>0" in one of the calculated columns.
It then treats the columns that are the addends in that equation
as if they were parameter queries. In other words, it asks me to
input values for the two columns that I am adding in that column.
If I take it a step back and input the <>0 on the criteria lines
for those two columns that are added together, it than asks me to
input values for the columns that are added together to calculate
those columns. What am I doing wrong!!!!!

The problem seems to be the following:
You calculate fields from calculated fields using something like:
Fidld1: some formula
Field2: some other formula
Field3: [Field1]+[Field2]
And you probably try to filter all records where Field3 <> 0.
Access tries to apply criteria before retrieving other fields and
doing calculations (which is good for the performance and bad for
your query...).
If you really need criteria for a calculated field (which is also
bad for the perfomrance, because no index can be used), you will have
to rewrite the expression for this calculated field so that it does't
use other calculated fields but only fields from the source tables
or queries.