Query returning null instead of 0 1

[edge70]

I try to count the quantity of participants that have 'Yes' values for a field but no participants have 'Yes' so I try to return 0 (zero) instrad of a null value.

I tried to use the following Nz as described in some other posts but it doesn't seem to work for me

Here's my query:

SELECT DISTINCTROW Nz(Count(*),0) AS [Compte De Table_Inscriptions]
FROM Table_Inscriptions
GROUP BY Table_Inscriptions.J1_PetitDejeuner
HAVING (((Table_Inscriptions.J1_PetitDejeuner)=Yes));

Can someone help me with this ?

 

[genomon]

Aren't DISTINCTROW & COUNT sort of logical opposites?
Null seems logical in this case....

Count should always return a number.

Show us some data that doesn't work here.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[edge70]

This is my problem, I try to count the 'yes' values of a boolean field named J1_PetitDejeuner in my table named Table_Inscriptions. When there is at least one 'yes' value I don't have any problem the count works fine and returns my the good values but as soon as no record are found I end up with a null value instead of '0'.

 

[dhookom]

Try:

SELECT Sum(Abs(J1_PetitDejeuner=True)) AS [Compte De Table_Inscriptions]
FROM Table_Inscriptions;

Duane
Hook'D on Access
MS Access MVP

 

[edge70]

Works fine !
Many thanks!!

Syl

 

[PHV]

Another way:
SELECT Count(*) AS [Compte De Table_Inscriptions]
FROM Table_Inscriptions
WHERE Table_Inscriptions.J1_PetitDejeuner=Yes

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[edge70]

Works also,thanks PH !

Syl