Set default to zero instead of Null

F

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.
 
F

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top