Criteria prevents Query from running.

M

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
 
M

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
 
Top