If null then display zero

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pdit - 06 Jul 2006 23:12 GMT

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");

Reply to this Message

Michel Walsh - 06 Jul 2006 23:48 GMT

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

>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.
[quoted text clipped - 5 lines]
> GROUP BY [Arrest table Query 2].Charges1
> PIVOT [Arrest table Query 2].[Adult or Juvenile] In ("Adult","Juvenile");

Reply to this Message