Null to Zero in Crosstab Query

G

Guest

Can anyone explain how to convert Null values to Zero in the Value field in a
Crosstab Query? I know how to use the Nz function but I don't know where to
use it to get it to work!

The Crosstab Query is based on an underlying Select query with aggregate
functions on fields. CountofFieldA is dragged from the Select query into the
design grid of my Crosstab query and I want the Sumof(CountofFieldA) to form
the Value in my Crosstab query and want any Null value for
Sumof(CountofFieldA) to show as zero. The Crosstab query runs perfectly but
frustratingly shows Null as Null.

=Nz([Sumof(CountofFieldA)],0) and =Sumof(Nz([CountofFieldA],0)) don't solve
the problem.

TIA
 
A

Allen Browne

Switch the query to SQL View (View menu in query design.)

You will see a line such as:
TRANSFORM Sum(tblInvoiceDetail.Quantity) AS SumOfQuantity

Add the Nz() there, e.g.:
TRANSFORM Nz(Sum(tblInvoiceDetail.Quantity),0) AS SumOfQuantity
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab Null value display as a "0" 2
Update a table from a query 7
Set default to zero instead of Null 3
Form based on crosstab query 1
problem with crosstab query 1
Crosstab: <> values not showing 7
Null values in a crosstab 2
Crosstab Query Questions 5

Top