NULLS to ZEROS in CROSSTAB QUERY

[laman]

Need some help with the followig query. I need to return 0 when nulls occur. Ive tried NZ and IIF and can't seem to get it to work.

TRANSFORM count([master].[ID]) AS CountOfID
SELECT [master].[SECTION], count([master].[ID]) AS [Total Of ID]
FROM master
GROUP BY [master].[SECTION]
PIVOT [master].[CL];

Please Help!
Thanks

 

[PhybrOptk]

I found some info on a website, it might help...

http://users.bigpond.net.au/abrowne1/casu-12.html

Try this and see if it fixes your null problem:

when using the nz you need it to tell nz to default to 0 if it = a null value

Nz([BlahBlah], 0)

maybe something like :

SELECT [master].[SECTION], count([master].[ID]) AS Nz([Total Of ID], 0)

help it helps

 

[laman]

I get a syntax error on left paren after nz.

 

[yored]

laman, try this link:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q132140

I used the following in my crosstab query and it worked (!!)

Field: IIF(Sum([Extended Price]) Is Null,0,Sum([Extended Price]))
Table: <leave blank>
Total: Expression
CrossTab: Value
Format : Currency

HTH
Red

 

[laman]

Yored The Man! Worked great, Thanks. I never would have tried that but I did try everything else.