Display a zero when no value 2

[ruthcali]

I am using Access97.

In my crosstab query, can i have Access display a zero when there is no value for that row? At the moment, it displays an empty cell.

Thanks,
ruth
ruth.jonkman@wcom.com

 

[GeekGirlau]

Have you tried using the NZ function?

 

[ruthcali]

Hi,
thanks for writing. but where in the crosstab query would i use the NZ function?

My crosstab query looks at the fields loc1 and loc2 (the 2 row headings) and looks at the field circuit class as the column and does a count of how many circuits can be found.

TRANSFORM Count(Circuit) AS [The Value]
SELECT Loc1, Loc2, Count(Circuit) AS [Total Of Circuit]
FROM tblCircuit
GROUP BY Loc1, Loc2
PIVOT CircuitClass In ("DS3","OC03","OC12","OC48"

Thanks

 

[CosmoKramer]

Ruth,

What field(s) do you want to do this to?? Loc1 & Loc2??

 

[ruthcali]

the Total field.
for example, now my results data looks like:

Loc1 Loc2 Total_of_Circuits DS3 OC3 OC12 OC48
AB XG 3 3
BG IF 7 1 5 1


And i want it to look like:
Loc1 Loc2 Total_of_Circuits DS3 OC3 OC12 OC48
AB XG 3 3 0 0 0
BG IF 7 1 5 1 0

 

[ruthcali]

the Pivot Count field.
for example, now my results data looks like:

Loc1 Loc2 Total_of_Circuits DS3 OC3 OC12 OC48
AB XG 3 3
BG IF 7 1 5 1


And i want it to look like:
Loc1 Loc2 Total_of_Circuits DS3 OC3 OC12 OC48
AB XG 3 3 0 0 0
BG IF 7 1 5 1 0

 

[ruthcali]

sorry, i hit Submit too soon on that post. and i think my column fields got mis-aligned.

I would like the zeros to appear on the Pivot Count field.

 

[CosmoKramer]

I don't know if this will work......Change the TRANSFORM statement to:

TRANSFORM IIf(IsNull(Count(Circuit)),0,Count(Circuit)) AS [The Value]

 

[ruthcali]

Cosmo--it works!! you're a genius. thank you so much. wow.