Null values in a cross tab query

[shannaj via AccessMonster.com]

I am trying to set my null values to zero with the nz function in my query
for monthly sales totals, but I can not figure how to use it. Here is my SQL,
can someone please help?

TRANSFORM Sum(qryCustomerSales2006.Amount) AS SumOfAmount
SELECT qryCustomerSales2006.[Customer ID], Sum(qryCustomerSales2006.Amount)
AS [Total Of Amount]
FROM qryCustomerSales2006 LEFT JOIN tblCustomerList ON qryCustomerSales2006.
[Customer ID] = tblCustomerList.[Customer ID]
GROUP BY qryCustomerSales2006.[Customer ID]
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul",
"Aug","Sep","Oct","Nov","Dec");

 

[Allen Browne]

Try changing the first line to:
TRANSFORM Nz(Sum(qryCustomerSales2006.Amount),0) AS SumOfAmount

To get Access to recognise the column as Currency, you probably want:
TRANSFORM CCur(Nz(Sum(qryCustomerSales2006.Amount),0)) AS SumOfAmount

More info:
Crosstab query techniques
at:
http://allenbrowne.com/ser-67.html

 

[shannaj via AccessMonster.com]

That worked! Thank you very much!

I am trying to set my null values to zero with the nz function in my query
for monthly sales totals, but I can not figure how to use it. Here is my SQL,
can someone please help?

TRANSFORM Sum(qryCustomerSales2006.Amount) AS SumOfAmount
SELECT qryCustomerSales2006.[Customer ID], Sum(qryCustomerSales2006.Amount)
AS [Total Of Amount]
FROM qryCustomerSales2006 LEFT JOIN tblCustomerList ON qryCustomerSales2006.
[Customer ID] = tblCustomerList.[Customer ID]
GROUP BY qryCustomerSales2006.[Customer ID]
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul",
"Aug","Sep","Oct","Nov","Dec");