Query Sum function; 0 + 0 = null?

[Guest]

Hi all,

Quick question, driving me a bit buggy.

Background:
I have several similar queries that do the following(just rough syntax but
you get the idea)

SELECT Sum(IIf([ColA]="red",1,0)) AS Red
FROM tblColors
WHERE (((tblColors.[colorDate])=[please enter the date for the report]));

Logic:
If the column has “red†in it for a particular date, put a 1, otherwise put
a 0, and add them all up in the end.

Problem: If there are no "reds", it puts all zeros which is good. But the
sum of which is sometimes null, and not 0, but this only occurs for some
queries, and of those queries only for certain weeks.

This inconsistency is odd because they are all pretty much identical with
the exception of red changes to yellow and so forth(They are literally copies
of one another).

Any suggestions? I know I can just use the NZ function but it still does
not address the issue as to why this is happening.

Any help would be greatly appreciated.

James D.

 

[Guest]

0 + 0 is not null as zero is a value and null is not a value. Your problem
is probably caused when you add a value to a null. So NZ is the solution.