How to use update query to update multiple fields in a table

[Rob]

Hello,
I am trying to update 6 fields in a table, but the update query only
performs the update on the first field in my query and not the others.
My query contains the 6 fields in the table and is simply trying to update
each record to 0 if null.

UPDATE [tbl BUDGET] SET [tbl BUDGET].[AP%] = 0, [tbl BUDGET].[SBJ%] = 0,
[tbl BUDGET].[EMEA%] = 0, [tbl BUDGET].[UK%] = 0, [tbl BUDGET].[LA%] = 0,
[tbl BUDGET].[FICE%] = 0
WHERE ((([tbl BUDGET].[AP%]) Is Null) AND (([tbl BUDGET].[SBJ%]) Is Null)
AND (([tbl BUDGET].[EMEA%]) Is Null) AND (([tbl BUDGET].[UK%]) Is Null) AND
(([tbl BUDGET].[LA%]) Is Null) AND (([tbl BUDGET].[FICE%]) Is Null));

Any ideas?

 

[Jerry Whittle]

First field or first record? First field makes no sense; however, I could see
it happeing with the first record.

With the ANDs in the WHERE clause, all six fields would need to be null
before any of them would be updated. If even field had data, that record
would not be updated.

Replacing them with ORs would be a bad idea as it would update all the
fields even if only one had a null. That would mess up perfectly good data in
the other fields.

Your best bet might be to split up the query into 6 update queries and run
them one after another to update one field at a time. You might also consider
making the default value for those files 0 so that you won't have this
problem with new records.

 

[John Spencer]

Well your query says to set all six fields to zero if and only if all six
fields are null. Is that what is happening? Is that what you want to
happen?