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

[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?

 

[Allen Browne]

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

 

[Guest]

Thanks, it worked perfect

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

 

[Guest]

I did the same thing but now i lose the currency format that the numbers
should be in. What should I do?

 

[Michel Walsh]

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

 

[Guest]

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

 

[Duane Hookom]

Nz() returns a variant. If you want to force to numeric, use"
TRANSFORM Val(Nz(SUM(something), 0)) AS theValue ...

 

[Guest]

It didn't work

here is what i typed in

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

whats wrong?

 

[Duane Hookom]

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]?