Replace null with zero in crosstab query

[Faberman]

Hello.

I have a simple crosstab query, which is designed to give a monthly total (numeric) and a yearly total for the value of orders placed by a number of customers.

The query is based on another query which simply extracts order records for a particular year.
For some customers, certain months will be blank as there were no orders placed.
I would like to use the crosstab query on a report, and I would like to replace the blanks with zeros.
I have searched for examples of query expressions using the nz function and attempted to apply some. I have, however, had no success in converting the blank monthly totals to zeros.

Can anyone help me achieve this?

The query is as follows :

TRANSFORM Sum(AmountQueryLastYear.SumOfAmount) AS SumOfSumOfAmount
SELECT AmountQueryLastYear.DeliveryName, Sum(AmountQueryLastYear.SumOfAmount) AS [Total Of SumOfAmount]
FROM AmountQueryLastYear
GROUP BY AmountQueryLastYear.DeliveryName
PIVOT Format([ShipmentDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

An example of expressions I have tried is :

Expr1: NZ([SumOfAmount],0)

In it's own column in the query design grid.

 

[reclusivemonkey]

Try;

IIF([SumOfAmount]="",0,[SumOfAmount])

NZ can give a zero-length string depending on the context.

 

[Faberman]

Thanks for coming back ReclusiveMonkey.

I have copied the expression into a column in the query design grid and set the "total" option to "Expression".
Running the query results in the same as before, a tabular result set with blanks where there are no values for a particular customer in a given month.

I have just looked at IIF, it seems to be used as much as nz, and seems to be as straightforward, except at the moment......

Do you have any further suggestions?
Anywill be welcomed.

 

[reclusivemonkey]

Try "IsNull" instead of ="" see if that works.

 

[EMP]

TRANSFORM Nz(Sum(AmountQueryLastYear.SumOfAmount))+0 AS SumOfSumOfAmount
SELECT ........


Have a look at this thread: Null value in Crosstab Query
http://www.access-programmers.co.uk/forums/showthread.php?t=64141

^

 

[Faberman]

WOW!!

Thanks you EMP - the thread reference you advised me of -

http://www.access-programmers.co.uk/...ad.php?t=64141

did the trick.

I altered the query to :

TRANSFORM nz(Sum(AmountQueryLastYear.SumOfAmount))+0 AS SumOfSumOfAmount
SELECT AmountQueryLastYear.DeliveryName, Sum(AmountQueryLastYear.SumOfAmount) AS [Total Of SumOfAmount]
FROM AmountQueryLastYear
GROUP BY AmountQueryLastYear.DeliveryName
PIVOT Format([ShipmentDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

and it worked.

Thank you.

Thanks also ReclusiveMonkey, for your suggestions.

 

[joshualee101]

Replace null with zero in crosstab query of size classes

I have a similar problem. I have a simple crosstab query for size classes. I need zeros for the size classes where there is no data. Otherwise that size class is not displayed. Anybody know of a simple way to do this?

SELECT AllYrsCL_SizeFreqNatHab.SiteName, AllYrsCL_SizeFreqNatHab.Species, AllYrsCL_SizeFreqNatHab.[Species Name], AllYrsCL_SizeFreqNatHab.Year, [Abalone Size Class Table].[Size Frequency Distribution], Sum(AllYrsCL_SizeFreqNatHab.count) AS SumOfcount
FROM [Abalone Size Class Table], AllYrsCL_SizeFreqNatHab
WHERE (((AllYrsCL_SizeFreqNatHab.Size) Between [min] And [max]))
GROUP BY AllYrsCL_SizeFreqNatHab.SiteName, AllYrsCL_SizeFreqNatHab.Species, AllYrsCL_SizeFreqNatHab.[Species Name], AllYrsCL_SizeFreqNatHab.Year, [Abalone Size Class Table].ID, [Abalone Size Class Table].[Size Frequency Distribution]
HAVING (((AllYrsCL_SizeFreqNatHab.Species)=9003 Or (AllYrsCL_SizeFreqNatHab.Species)=9004) AND ((AllYrsCL_SizeFreqNatHab.Year)=2008 Or (AllYrsCL_SizeFreqNatHab.Year)=2009));

 

[conclavechorus]

Thank you Faberman & EMP!

I had the same problem with null values resulting from a crosstabs query interfering with subsequent calculations. Thanks to Faberman posting his existing AND altered SQL using the solution suggested by EMP, I was able make my database function properly.

I registered just to say a big THANK YOU!!