Null fields need to be zeros - any suggestions?

[Tia]

I have a very large table of columns of numbers which get calculated. My problem is I import this data from an external text file and where there should be zeros, they are null, so the calculations are off. If I replace the null fields with zeros, the calculations work. I can't seem to figure out a way to update all of the nulls with zeros in one step. I don't want to write a separate update query for every column (since it's 24-36 columns at a time). If I use the "and" criteria, it only updates the columns where ALL of the columns contain null fields. If I do the "or" criteria, it updates everything to "0.00" if ANY column has a null field

Does anyone have a suggestion? Please email me if you do...

 

[Douglas J. Steele]

You can use the Nz function, and update all columns at once. You don't even
need a WHERE clause.

The SQL would look something like:

UPDATE MyTable
SET Field1 = Nz(Field1, 0,
Field2 = Nz(Field2, 0)

etc.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I have a very large table of columns of numbers which get calculated. My

problem is I import this data from an external text file and where there
should be zeros, they are null, so the calculations are off. If I replace
the null fields with zeros, the calculations work. I can't seem to figure
out a way to update all of the nulls with zeros in one step. I don't want
to write a separate update query for every column (since it's 24-36 columns
at a time). If I use the "and" criteria, it only updates the columns where
ALL of the columns contain null fields. If I do the "or" criteria, it
updates everything to "0.00" if ANY column has a null field.