Displaying Zeros in Crosstab Queries

  • Thread starter Thread starter grantschneider
  • Start date Start date
G

grantschneider

I am trying to get my crosstab query to display zeros where there are
no values. I cannot figure out where to put the expression
 
Switch the crosstab to SQL View (View menu.)

That start of the SQL statement reads something like this:
TRANSFORM Sum(tblInvoiceDetail.Quantity) AS SumOfQuantity

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

JET probably won't understand the data type corrrectly after that, so I
suggest you use CLng(), CDbl() or CCur() around that expression - depending
if your data is Long, Double, or Currency. Example:
TRANSFORM CCur(Nz(Sum(tblInvoiceDetail.Quantity),0)) AS SumOfQuantity

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

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

Fill Blanks with Zero on CrossTab 4
Crosstab queries, <> and null values 3
crosstab query 3
cross tab query fill with zero 3
Crosstab queries - populating blanks with zeros 3
Crosstab query as data source for update query 7
crosstab parameterized query 1
Parameter Value in Crosstab Query 4

Back
Top