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
 
Back
Top