I have a "Yes/No" field in a table and I want to count the "Yes's" with a query.
I have three records as "yes"
If I use "Count" and a criteria of "False or No" it returns null.
If I use a criteria of "True or Yes" it returns the total records in the table.
If I use "Sum" and a criteria of "True or Yes" it returns -3, negative 3.
If I use a criteria of "True or Yes" it returns null.
I could use "sum" and multiply by -1 but that seems a bit ridiculous.
Where am I going wrong?
I know I can use dCount to get the right answer but doing so creates other problems with the interchangeability of the code.
I have three records as "yes"
If I use "Count" and a criteria of "False or No" it returns null.
If I use a criteria of "True or Yes" it returns the total records in the table.
If I use "Sum" and a criteria of "True or Yes" it returns -3, negative 3.
If I use a criteria of "True or Yes" it returns null.
I could use "sum" and multiply by -1 but that seems a bit ridiculous.
Where am I going wrong?
I know I can use dCount to get the right answer but doing so creates other problems with the interchangeability of the code.
Code:
SELECT Count(Main.Label) AS CountOfLabel
FROM Main
HAVING (((Count(Main.Label))=Yes));
SELECT Sum(Main.Label) AS SumOfLabel
FROM Main
HAVING (((Sum(Main.Label))=Yes));