zero value on query causes report not to populate 1

[kentwoodjean]

Tried the search function but it is under maintenance right now.

Have a cross-tab query with 4 fields, 2 are titled RespDef and Count of RespDef. My report works beautifully if there is a subsequent count in each of the RespDef codes of which there are about 8. If one of them has a zero entry, the whole report is blank. If I remove the Text boxt for that missing respDef, the report will populate. What must I do to my query so that on a given day, whether there be a 0 entry or a count of entry, the report will print. Hereis my SQL:

TRANSFORM Sum(FacilityPendingCatSum.[SumOfCountOfContract #]) AS [SumOfSumOfCountOfContract #]
SELECT FacilityPendingCatSum.RespCat, Sum(FacilityPendingCatSum.[SumOfCountOfContract #]) AS [Total Of SumOfCountOfContract #]
FROM FacilityPendingCatSum
GROUP BY FacilityPendingCatSum.RespCat
PIVOT FacilityPendingCatSum.RespDefID;

 

[GingerR]

try using the NZ() function.

i.e. nz(blah,0)

if blah is null, it will make it zero instead.

g

 

[kentwoodjean]

Thanks, that did the trick.