Need count function to show 0 in crosstab report

[Guest]

I created a crosstab query that counts transaction types for parts in a
transaction log table. Some parts may not have a particular transaction
type. The count is showing a null as opposed to a zero. I want to show
zeros on the corresponding report. Is there any way to make that happen?

 

[Duane Hookom]

Please share your SQL view. It makes answering much easier.

Try a value like:

TheValue:Val(Nz(Count([a field]),0))

 

[Allen Browne]

Open the query in SQL View (View menu), and add Nz() around the expression
in the TRANSFORM clause to specify a value for Null.

JET is then likely to misinterpret the data type of the coluums, so you
probably want to typecast as well.

You will end up with something like this:
TRANSFORM CLng(Nz(Sum(tblInvoiceDetail.Quantity),0)) AS SumOfQuantity

If the typecasting issue is new, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html