One Update Query to Update several fields simultaneously

[Jessica240]

Hello all! Thank you in advance for your help to what seems a rather simple problem.



I have one table with several blank records in different fields. I simply need anything that is blank to be replaced with a zero. For some reason, when I try to design the query to update several fields at once, it does not work. I have attached 2 screen shots: in the design view, you can see that I have put "0" in the update to area. And I put "Is Null" for the criteria. But then, if you look at the table view, you can see that after I ran the query, some records were not updated - they are still blank.

Also I have noticed that the more fields I add to my query design, the less rows get updated. For example, when I try only updating one field, all 9 records get updated. But when I try updating 5 fields and click "run" then the window will pop up saying I'm about to only update 7 rows.

I'm not very familiar with SQL so if anyone could help me out with the design view that would be great! Thank you!

[lfpm062010]

Use NZ(TABLENAME.FIELDNAME, 0) in place of "UPDATE TO" are.
You don't need the criteria "IS NULL"

http://www.techonthenet.com/access/f...dvanced/nz.php

[msaccess2&beyond]

Currently you are saying that all three have to be null at the same time.

The previous note is better than mine.

If you do this over and over you could do three separate updates and then put all three updates in a macro.

Obviously, Not as efficient as the first poster.

Sorry for my change as the last option was wrong.

[Jessica240]

Thank you both! I didn't realize that by putting in "is null" in all three fields' criteria, that that would require them all to be null at the same time. Very good to know. Problem solved!