Aggregate count function

I've created a query that counts the number of records based on a criteria of Yes or No (for a Yes/No data type). Is there any way to have the results display a zero (or something) when there are no Yeses or Nos?

CM

--
Message posted via http://www.accessmonster.com

I guess I am missing something here. How can you have zero yes and zero no in a
recordset? A yes/no field has to have one value or the other.

Are you applying some criteria and therefore returning zero records in the recordset?

"Chris Motherwell via AccessMonster.com" wrote:

I've created a query that counts the number of records based on a criteria of Yes or No (for a Yes/No data type). Is there any way to have the results display a zero (or something) when there are no Yeses or Nos?

CM

--
Message posted via http://www.accessmonster.com

Thanks for the reply John.
Let's say the criteria is Yes for a Yes/No field. If there are no Yeses in the the data, is it possible to display a zero to show this result?

CM

--
Message posted via http://www.accessmonster.com

Can you post the query you are using? Or are you using one of the VBA aggregate
functions? A very basic query might look like:

SELECT Sum(IIF(SomeYN=True,1,0)) as CountYes,
Sum(IIF(SomeYN=False,1,0)) as CountNo,
Sum(IIF(SomeYN2=True,1,0)) as CountYes2,
Sum(IIF(SomeYN2=False,1,0)) as CountNo2,
FROM SomeTable


"Chris Motherwell via AccessMonster.com" wrote:

Thanks for the reply John.
Let's say the criteria is Yes for a Yes/No field. If there are no Yeses in the the data, is it possible to display a zero to show this result?

CM

--
Message posted via http://www.accessmonster.com

I had to nest the queries to get it to work - the Sum and Iif combined did the trick! Thanks a lot

Chris

--
Message posted via http://www.accessmonster.com