Previous Thread

7/6/2006 3:12:02 PM    If null then display zero
I have a crosstab query that I need to display zeros if the field is blank. 
 
Here is my SQL statement. Any help is appreciated. 
 
TRANSFORM Count([Arrest table Query 2].[Arrest date?]) AS [CountOfArrest 
 
date?1] 
 
SELECT [Arrest table Query 2].Charges1 
 
FROM [Arrest table Query 2] 
 
GROUP BY [Arrest table Query 2].Charges1 
 
PIVOT [Arrest table Query 2].[Adult or Juvenile] In ("Adult","Juvenile");



7/6/2006 6:48:16 PM    Re: If null then display zero
Hi, 
 
TRANSFORM NZ(Count([Arrest table Query 2].[Arrest date?]),0)  AS 
 
[CountOfArrest 
 
date?1] 
 
SELECT [Arrest table Query 2].Charges1 
 
FROM [Arrest table Query 2] 
 
GROUP BY [Arrest table Query 2].Charges1 
 
PIVOT [Arrest table Query 2].[Adult or Juvenile] In ("Adult","Juvenile"); 
 
I added a NZ(   , 0)  around the COUNT(). 
 
Hoping it may help, 
 
Vanderghast, Access MVP 
 
"pdit" <pdit@discussions.microsoft.com> wrote in message 
 
news:8BA37DFC-ABF8-478E-9ECD-1A39FB362B4F@microsoft.com...