0 values ina crosstab query

[Billkamm]

Hi, I have a crosstab query, but for any product that has no entries on a certain date a NULL value is put in its place. Is there a way to have a 0 display there? Example of NULL:

	Date		PRODA	PRODB	PRODC	PRODD	PRODE
	08/01/2005	1		36	3	3
	08/02/2005	1	1	26	4	3
	Aug Total:	2	1	62	7	6

See how there is a NULL for 8/1 under PRODB? I would like that to be a 0

 

[john471]

Can you post the SQL for what you are currently getting ?

 

[mresann]

Check the Nz function for the field that supplies the top row.

 

[bazzabat]

Yeah Use Nz for this: An example is:
Prod_B: Nz(Sum([ProdB]))+0

I had trouble getting the Nz Funtion to work correctly, but when I got the brackets in the correct place it worked well. In this case I have used a Label for the Column heading.

Am not on often so hope this helps

 

[john471]

The default action of the NZ Function is to return a zero length string, not in fact zero. The optional parameter allows you to specify a value to substitute for Null.

Sum(NZ([ProdB],0))

May work better.