Access 2010 Update blank fields to zero

However, if the field is actually a string, and you want to test for NULLs or zero length strings, and replace those with a zero ("0")  then you might try:

UPDATE yourTable SET [SomeField] = "0" WHERE [SomeField] & "" = ""

This syntax appends a zero length string to [SomeField], the result of which will be a zero length string if [SomeField] IS NULL or if [SomeField] = ""

Thanks for your suggestions.

All fields in questions are numeric.  They came into numeric fields as blank when the data was brought in.

Thanks again.

Timothy

Simple and worked great.

Thanks

Timothy

If you are new at this, the best way to do this is one field at a time, with the syntax mentioned above (first example should do it).  

You can do it more than one at time, but the syntax is significantly different, and there is greater potential for overwriting the data that exists in the other records/fields.

Fyed,

Thanks for the advise.  I will do it one field at a time.  I was beginning to try more than one but was skittish.

Thanks again.

Timothy

I strongly recommend that you always work with a backup copy of your data.

When you do more than one field at a time, you have to construct the SQL so that it won't replace a value in one field that shouldn't.  By using the NZ( ) function you can pass it the value of a field, and if that field is not NULL, then it will return the value that was passed.  If the field IS NULL then the function will return the value you assigned to the 2nd argument in the function (in this case a zero).

The syntax would look like:

UPDATE yourTable
SET  [Field1] = NZ([Field1], 0)
       ,[Field2] = NZ([Field2], 0)
       ,[Field3] = NZ([Field3], 0)

You don't really need a WHERE clause for this because it will actually replace each value in each of the fields, of every row in your table.

If you have a lot of data, you might add a WHERE clause that looks like:

WHERE [Field1] IS NULL OR [Field2] IS NULL or [Field3] IS NULL

Fyed,

Thanks so much.

You multi-field code works great without the WHERE clause.

The IS Null or ISNUll clause gives the Where clause problems.

Thanks again.

Timothy

glad to help.