I have a crosstab query.
TRANSFORM Sum([tblSales].[Quantity]) AS SumOfQuantity
SELECT [tblSales].[Area], Sum([tblSales].[Quantity]) AS Total
FROM tblSales
WHERE ((([tblSales].[Year])="2003"))
GROUP BY [tblSales].[Area]
PIVOT [tblSales].[Quarter];
It contains some null values. I need to use the figures in subsequent calculations.
Is it possible to change the null values into 0 in the crosstab query?
I thought Nz(...,0) would work. So I tried Nz(Sum([tblSales].[Quantity]),0) in the database attached, but it turned all the figures into TEXT except for the Total column.
TRANSFORM Sum([tblSales].[Quantity]) AS SumOfQuantity
SELECT [tblSales].[Area], Sum([tblSales].[Quantity]) AS Total
FROM tblSales
WHERE ((([tblSales].[Year])="2003"))
GROUP BY [tblSales].[Area]
PIVOT [tblSales].[Quarter];
It contains some null values. I need to use the figures in subsequent calculations.
Is it possible to change the null values into 0 in the crosstab query?
I thought Nz(...,0) would work. So I tried Nz(Sum([tblSales].[Quantity]),0) in the database attached, but it turned all the figures into TEXT except for the Total column.
