Update Query to replace blanks

[GraemeG]

Hello,

I have a query update which is trying to replace blanks within my table.
However I can do this for one field. But when I try and use the same query update for multiple fields it won't update. I take this to be because it will only update if all the particulars are blank for the one record. However this is not the case I have some blanks and some not blanks.
If I were to do an update for every field there would be 500+. Is there anyway round this?

Thanks

 

[mahenkj2]

UPDATE TableName SET TableName.FieldName = "YourWord"
WHERE (((TableName.FieldName) Is Null));

or set Is Null in criteria of update query in the field you are interested in.

regards.

 

[Trevor G]

Hi GraemeG

You would need to use some VBA to do this as you will need to look at editing. I would ask why are you looking to fill empty fields in so many fields?

 

[mahenkj2]

First I thought, you just need one field for update.

To fill many fields together, you may use OR field in query design view and next rows further like shown in image attached.

 

[GraemeG]

hi thanks for all the replys.

Trevor G - I am filling in so many blanks as the data capturing device is a form which only have to answer certain questions, therefore leaving the other fields blank. However I now need to create a query which uses caluclations and it will not show records and caluclate on so many blanks - uncluding using Nz. So my way around this was to update the tables and overwrite the blanks. Allowing this to work.

Mahenkj2 - thanks this works, although the query will only do it for max 10 fields so this will mean making 50+ update queries.

Thanks

 

[MSAccessRookie]

First I thought, you just need one field for update.

To fill many fields together, you may use OR field in query design view and next rows further like shown in image attached.

As I see it, this design would update ALL THREE Fields whenever ANY ONE OF THEM is blank, and I do not think that this what the OP wants. Am I missing something?

 

[Trevor G]

Take a look at this link it should give you what you want.

http://www.jpsoftwaretech.com/remove-nulls-all-fields-database/