Update Query Problem

[Jessica240]

Hello All! I am trying to update several fields all at once in one single query. Also, I can only use the Design View (No SQL)

The weirdest thing keeps happening though: The more fields I enter to get updated, the less rows get updated.

For example,

When I have only one field that gets updated and I click "run" the window pops up saying I'm about to update 9 rows. It asks me if I'm sure I want to update those records so I say "no".....

Then, I add in another field and click "run" and the window pops up daying I'm about to update 8 rows. When it asks me if I'm sure I want to update those records, again I say "no"......

Then, I add in another field and this time it says 7 rows..... and the trend goes on and on until it says I'm going to update 0 rows.


What the heck is going on?????? I have about 15 fields I need to update all at once. I don't get why its not letting me update them simultaneously.

Any help would be greatly appreciated!

Thanks!

Jessica

 

[pbaldy]

A picture in design view may help (or the SQL from SQL view), but my guess is that you're adding a criteria each time.

 

[Jessica240]

My goal is to have every blank record in my table to be updated to a zero. So in the design view of my update query, where it says "update to:" I put "0" and for the criteria I have "is null".

I have attached 2 screen shots. I've been experimenting with it, trying different things to get the results I want. In the query that I attached a screen shot of, you can see that I wanted to update everything in field "28104 PAYABLE" to "0" if it was blank... but when I ran the query, some blanks were not updated. I attached a screen shot of the table I updated after I ran the query. As you can see by the records I highlighted, the query did not update everything as I needed it to. I'm not sure why it is only updating some of the records and not all of them.

 

[pbaldy]

If we can back up a step, what do all the fields represent? All the numbered "Payable" fields make me suspect a design problem (normalization). You can work around it by not having a criteria and setting every field:

Nz(FieldName, 0)

but again, I think there's a design problem.

 

[Jessica240]

The Nz function worked perfectly! Thats what I needed all along but I didn't know about that until now. Thank you so much!

 

[pbaldy]

No problem!

 

[sumox]

you can use SQL Len function to detect and act thru IIF function by adding
critieria

i havent tried yet !