Counting zeros in a crosstab report

[Tivoli0]

Hi All,

I'm using Access97 & Win95

I have a report that is based on a crosstab query that returns a row for each OBJECTIVE and a column for each GOAL.
The intercept is the counted number of OBJECTIVES in that GOAL. Here is the code:

SELECT DISTINCT [To Do].Objective, Count([To Do].Objective) AS CountOfObjective, [To Do].Goal
FROM [To Do]
WHERE ((([To Do].Completed) Between [Start Date:] And [End Date:]))
GROUP BY [To Do].Objective, [To Do].Goal;

My problem: If the CountOfObjective is null (no value) I get a zero and NO count for that. I do want to count the zeros as it tells me how many COMPLETED tasks have no OBJECTIVE description.

Do I have to create a funcion? How? I'm not that guru and will appreciate any help/code!

TIA, -Ami

 

[67peshawar294]

Hi Tia,
Try a text box with
=IIf(IsNull([nameoffield]),0,[nameoffield])
and a txtbox in report footer with
=sum(IIf(IsNull([nameoffield]),0,[nameoffield]))
I had the same prob with a crosstab and this solved not counting nulls.
Jim