Conversion - Null Values

[destinman via AccessMonster.com]

I have a table that is basically a duplicate of my printed Aged Rec schedule.
Values are updated to this table through queries that change data depending
on the date used. The problem I am having is that there are periods where a
field in the aging table is null. How can I get a default " 0.00" to be
entered into the field instead of the null value. Of course, this null value
screws up any future calculations based on this table. I have tried to set
the default value to "0.00" in the table design but this does not seem to
work.


Thanks for you help !!

 

[Allen Browne]

In a query, you can use IIf() or Nz() to supply a value for Null.
For example, to use zero for null in a field called Amount, type:
IIf([Amount] Is Null, 0, [Amount])

You need to do that if you are trying to sum fields across a table, e.g.:
AmtInc: IIf([AmtEx] Is Null, 0, [AmtEx]) + IIf([Tax] Is Null, 0, [Tax])

You don't need to do that if you are summing a column. In fact, the nulls
are really important to results in a column. If you create a Totals query
(depress the Total button on the toolbar in query design), and count a
field, Access gives you the number of records where there really is
something in the field. If you actually store a zero in the field when it
should be null, your count will be wrong. Similarly, calculating averages
will give skewed results if you actually store a zero where the value is
unknown/not applicable (and so should be Null.)

Anyway, if you really want to update existing nulls to zeros, use an update
query (Update on Query menu, in query design.) Access adds an update row to
the grid. In this row under your field, enter:
0
and in the Criteria row:
Is Null

But in my experience, Nulls are *really* important, and worth the effort to
understand how to work with them rather than around them.