I have a crosstab query that uses a Count as the Value. Sometimes I get null value in my crosstab query results. Is there any easy way to change my SQL so that my nulls appear as a 0 in my crosstab results?

Here is a simple sample SQL of a crosstab:

TRANSFORM Count([ZooTable].[AnimalNames]) AS ([CountOfAnimalNames])
SELECT [ZooTable].[AnimalTypes]
FROM ZooTable
GROUP BY [Zootable].[AnimalTypes]
PIVOT [ZooTable].[ZooLocation];


Thanks for reading my post! Teddy

Replace the field with

IIF(Isnull([ZooTable].[AnimalTypes]),0,[ZooTable].[AnimalTypes])

and it should work

TRANSFORM CLng(NZ(Count([ZooTable].[AnimalNames]),0)) AS ([CountOfAnimalNames])
SELECT [ZooTable].[AnimalTypes]
FROM ZooTable
GROUP BY [Zootable].[AnimalTypes]
PIVOT [ZooTable].[ZooLocation];

Thanks for the reply Guys!

John, I ended up using your solution. Always a pleasure to see your reply on one of my posts!

I always forget about the Nz function, Johns was the better solution.