Updating all Null valued fields to 0

[cooltom]

How can i update all the null valued fields of a table to zero?? or is there any other method??? by the way i am using access 2000.

 

[Rich]

Use an update query, set the criteria line of the field you wish to update Is Null

 

[cooltom]

What is the correct code or sysntax for that??

 

[Rich]

Create a new query, add the table add the fields you require, change the query to an update by selecting the option from the drop down list on the menu bar, on the update to line put 0, on the criteria line put Is Null ,try it on a copy of your table first.
SQL will look something like UPDATE TableName SET TableName.SomeField = 0 WHERE ((TablName.SomeField) Is Null);
HTH

 

[cooltom]

Thanks but that i have aleady done. how can i update muliple fields (of every record).Update query uses a where cluase with AND operator!! please try it on a sample database and you will know what i mean!!!!!

 

[jwindon]

There is an argument in the Update Query design grid of "OR".

Include the Is Null under the other fields you wish to update on this line of the arguments.

 

[Pat Hartman]

If you want to update multiple fields using different criteria in the same query, you need to ditch the where clause. You'll need to use the Nz() function instead.

UPDATE TableName SET TableName.SomeField1 = Nz(Somefield1,0), TableName.SomeField2 = Nz(Somefield2,0);

The Nz() function will return 0 if the field is null otherwise it will return the fieldvalue. The above update query will update SomeField1 and SomeField2 for EVERY row in the table, regardless of their initial values. It will either replace the values with the existing value or 0.

[This message has been edited by Pat Hartman (edited 11-21-2001).]