Cross Tab Query Nulls

[Darth Vodka]

is there any way to make a cross tab query default to zero instead of nulls for unpopulated cells

i know i can Nz all the resultant fields, but it seems like treating the symptoms and not the cause...

 

[Guus2005]

Deny null values in your tables. I would use Nz however.

 

[Jon K]

Try using Nz(....)+0 in your crosstab query e.g.

TRANSFORM Nz(Sum([FieldName]))+0 AS SumOfFieldName

.

 

[Darth Vodka]

Try using Nz(....)+0 in your crosstab query e.g.

TRANSFORM Nz(Sum([FieldName]))+0 AS SumOfFieldName

.

didn't work

no matter, i have given the report the formatting

#,##0;(#,##0.00);0;\0

to deal with it

thanks anyways

 

[Jon K]

It's strange. I suggested this solution to others more than once and I got
feedback that it worked.

http://www.access-programmers.co.uk/forums/showthread.php?t=64141&highlight=Transform

http://www.access-programmers.co.uk/forums/showthread.php?t=79457&highlight=Transform

.

 

[Darth Vodka]

aha...got it

sorry, i was doing every single combination of NZ, Sum and zero except the one that works

many thanks, that's very handy