Query and Count no records Want a Zero

  • Thread starter Thread starter Zach
  • Start date Start date
Z

Zach

I am trying to do a simple query and count at the same time and if I don't
have any results that match it doesn't give me a value of zero. It doesn't
even give me a row. What is my solution?
 
You probably use a where clause. The WHERE clause is evaluated first, before
the GROUP/COUNT, and if no record passes the condition, no record would
return.


You can use:

SELECT SUM(iif( condition, 1, 0)) FROM table


or

SELECT COUNT(*) FROM (SELECT * FROM table WHERE condition )




If you have a lot of records, the second expression could be faster, since
it MAY use indexes, if any is available for the condition, while no index
could be used in the first case.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top