changing blank fields to zero

  • Thread starter Thread starter MK
  • Start date Start date
M

MK

I'm importing data into a table and some of the fields are blank (""). Those
that are imported into a currency field need to be changed to 0 for the form
so that I can calculate some values (i.e. if they stay as Null then adding
50 to Null gives me Null, but I want that to be 50+0=0. Default value was
set to zero, but that doesn't seem to matter. I tried a macro to set fields
="" or Null to 0, but I had no luck. And I read that comparion with Null
values don't work. How can I best do this?
Thanks
Michael
 
Try an Update Query setting the where clause to
SET <currencyfieldsname> =0
WHERE <currencyfieldsname> IS NUll
 
I'm importing data into a table and some of the fields are blank (""). Those
that are imported into a currency field need to be changed to 0 for the form
so that I can calculate some values (i.e. if they stay as Null then adding
50 to Null gives me Null, but I want that to be 50+0=0. Default value was
set to zero, but that doesn't seem to matter. I tried a macro to set fields
="" or Null to 0, but I had no luck. And I read that comparion with Null
values don't work. How can I best do this?
Thanks
Michael
Click to expand...

regarding: >but I want that to be 50+0=0.<
Is that really what you want, or did you mean =50?

Look up the Nz() function in VBA help.
=Nz([FieldName],0) + Nz([FieldName],0)
 
Michael, you can update the nulls to zero like this:
1. Create a query into this table.
2. Change it to an Update query (Update on Query menu.)
3. Drag the field into the grid.
4. In the Criteria row under this field, enter:
Null
5. In the Update row under this field enter:
0
6. Run the query. (Exclamation icon on toolbar.)

Before you do that, though, nulls are conceptually very different from
zeros. Null means the value is unknown (or does not apply.) For example
saying the value of an item is unknown (null) is not the same thing as
saying that the item has no value (zero.)

There are certainly cases where you don't want to allow nulls, but you need
at least to be aware of the difference. Another exmaple: if you ask Access
for the average of:
12, 12, 0
the answer is 8, i.e.:
(12 + 12 + 0) / 3.
On the other hand, the average of:
12, 12, Null
the answer is 12. The unknown value is ignored, so it calculates as:
(12 + 12) / 2

Where you don't want to convert the values to null, but you do want to do
the calculations, you can use Nz(), e.g.:
=Nz([Amount1],0) + Nz([Amount2],0)
 
Thanks. That works. If I want to do several fields, do they all need
separate update queries?
 
Yes. Some fields may be blank in some records and not in others, so the Is
Null criteria can only be applied safely to one field at a time.
 
Allen said:
nulls are conceptually very different from
zeros. Null means the value is unknown (or does not apply.)
Click to expand...

Not quite. NULLs are conceptually very different UNKNOWNs.

NULL is a 'placeholder' for a data value that is missing. NULL is a
data value.

UNKNOWN is generated when a NULL is used in a calculation or logical
expression. UNKNOWN is a logical value.
 
Back
Top