In my crosstab query I want it to enter a "0" for null values.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm running a crosstab query and in my sum column I want it to put a zero if
there is no value. I know how to do it with regular queries, but am running
into problems with a crosstab. Can it be done?
 
Switch the query to SQL View (View menu), and add Nz() around the
expresssion in the first line.

It will end up something like this:
TRANSFORM Nz(Sum(tblInvoiceDetail.Quantity),0) AS SumOfQuantity
 
Thanks, it worked perfect

Allen Browne said:
Switch the query to SQL View (View menu), and add Nz() around the
expresssion in the first line.

It will end up something like this:
TRANSFORM Nz(Sum(tblInvoiceDetail.Quantity),0) AS SumOfQuantity
Click to expand...
 
I did the same thing but now i lose the currency format that the numbers
should be in. What should I do?
 
Hi,


I don't see the initial thread, but changing:


TRANSFORM SUM(something) AS theValue ...


into


TRANSFORM Nz(SUM(something), 0) AS theValue ...


should do. You have to be in SQL view, to see the SQL statement.



Hoping it may help,
Vanderghast, Access MVP
 
The o come into the table but i lose the currency format. (see samples below

Before

budget line 2001 2002 2003 2004
a $3,548 $353
b $567 $356 $4,234
c $669 $165

After

budget line 2001 2002 2003 2004
a 3548 0 353 0
b 0 567 356 4234
c 0 669 165 0
 
Nz() returns a variant. If you want to force to numeric, use"
TRANSFORM Val(Nz(SUM(something), 0)) AS theValue ...
 
It didn't work

here is what i typed in

TRANSFORM Val(Nz(Sum(Query2.money),0)) AS SumOfmoney

whats wrong?
 
I'm fairly sure it worked since I didn't see any error message or results
that were wrong ;-)
You might want to share your full SQL view as well as your reason for
thinking "it didn't work".
What happens if you put brackets around [Money]?
 
Back
Top