Set default to zero instead of Null

[Fred Zack]

Isn't there a way to set some default to put zero in a cell or calculation
instead of null. I am trying to get zeros as values in a Crosstab Query and
can't seem to find a way to do it.

I realize I could do the crosstab query, save as table, use that table and
do a default, etc. etc.; but this seems awfully convulated. I need to do
average claculations where a zero value is maningful (e.g., average counts
of a response over 10 subjects).

Just seems I should be able to tell Access I want a general default of 0 in
all number cells not null.

 

[Allen Browne]

See:
Crosstab query techniques: Display zeros
at:
http://allenbrowne.com/ser-67.html#DisplayZero

 

[Fred Zack]

Haven't used the SQL capability yet. Was looking for somehing easier. Maybe
it doesn't exist in Excel. Just want zero in all calculations instead of
null. No way to just set this up once for my whole DB, or at least a crosstab
query? without getting into SQL?

 

[Allen Browne]

No: there is no switch in Access to display zeros everywhere there are
Nulls.

The Nulls are actually significantly different to zeros in their meaning, so
just substituting zeros for them would give wrong results for many
calculations. For example, the average of 3, 9, 0 is 4, i.e. (3 + 9 + 0) / 3
= 4.
However, the average of 3, 9, Null is 6, i.e. (3+9) / 2 = 6.

It's easy enough to modify your crosstab.
In query design, just choose SQL View on the View menu.

The other alternative is to use the Format property of the text box in your
report. You can make the text box show a zero for Null if you supply the 4
parts of the formatting, and this doesn't mess up the calcuations.