Nz Question. (2000 (SR-1))

Topic

New Reply

I have a large table with 300 rows and 40 columns.

Is there a way to format an update query to change all nulls in all columns to zero?

OR

How can I change all the nulls to zero?

Reply | Quote

Replies

Hi,
You can run an update query where you update each field to:
Nz([fieldnamehere],0)
I assume all your fields are numeric? Otherwise you might want to choose something else (e.g. “”) to update text fields to.
Hope that helps.

Reply | Quote

In the criteria line of each column, put IS NULL; in the ‘update to’ line of each column, put 0 (Zero). Copy your table first, then try this.

Reply | Quote

Be VERY careful. Rory’s solution is the only safe one that will selectively update all the null fields in a single pass without tripping over the internal limits on OR levels in a query. However, if you have a lot of nulls, don’t expect it to be fast.

Reply | Quote