Replace all null-values in a table to zero-values at once?

[Leen]

Hi,

I have a table with several (many) fields and for each field I have some empty values (cells). For now, I made an update-query for each field, for example: IIf(IsNull([Man1Type]),"0",[Man1Type]).

This works, however, as there are many fields I was searching for an automatic code (using VBA?) to replace all empty cells in the large table at once. I tried to use the function Nz() but without succes (you also need to do it for each field in a query wich comes to the same as the solution above).

Does someone has an idea how to automate this?

Thanks a lot on forehand!
Leen

 

[MarkK]

In table design set the default value of the field to zero.

 

[gemma-the-husky]

thats what an update query does!

for each column thats a problem, put the word "null" in the criteria row - access will change it to "is null".

then in the update row, put 0 or "" whatever else you need

no vba needed

--------------
changing the validation test in the table will work in future, but fail against existing data

 

[Leen]

Reply 2 methods

Thanks,

*using the first method mentioned: you can put 0 as a default value in a Table, but that doesn't make all empty values in a table 0 (it does for new rows your addig but not for existing rows). Correct me if I'm wrong.

*using the second method using updatquerys goes indeed faster, only, it remains somewhere the same problem: you have to do it field by field. So impossible to do it for the whole table at once?

Anyway, I'm already a step further with your help.Thanks!

Leen

 

[ListO]

You can do it with a query.

The query can be set up to update every field you wish to.

In the query design window, enter a colum for each field that you want to act upon.

Enter your desired replacement value ("0" or "") in the Update To: row of each column.

In the first column, enter "is null" in the Criteria: row.
In the second column, enter "is null" in the Or: row.
In the third and subsequent columns, enter "is null" in the next available Or: row (every row below the first OR row is another OR row).

Complete the pattern.

Good Luck!