Criteria prevents Query from running.

[Mike]

I have a query that runs to calculate to cost per attendee at a
function. The expression I use is:

CostperAttendee: IIf(Nz([Total],0)=0,[Cost],[Cost]/[Total])

It runs just fine and gives me the cost per attendee but I want to add
a criteria that only brings back functions that the cost per attendee
is greater than 100.

When I add >100 to the criteria I get a box asking for Total.

Any one have any ideas on why or how to correct?

Please don't respond to my email address. (I won't get it)

Thanks!

Mike Meyering

 

[Guest]

Post your SQL.

 

[Mike]

Its not in SQL it is just in the query expression box.

Thanks!

Mike Meyering

 

[Michel Walsh]

[Total] is probably a computed expression, which is not allowed in the WHERE
clause. In the query designer, Access let you use a criteria, under a
computed column, but, if you look at the generated SQL statement, the WHERE
clause use the expression, not the alias:


SELECT ..., a+b as c, ...
FROM ...
WHERE a+b <100


*** but NOT *** :

SELECT ..., a+b as c, ...
FROM ...
WHERE c <100


So, edit the SQL statement and change [Total] in the WHERE clause for the
expression on which [Total] is used as alias.


Vanderghast, Access MVP

 

[Mike]

Yep that did it! Okay, how many hours did I just spend on that!

Thanks!