Replace Null Values

[ootkhopdi]

Hi

I have a access 2013 table with more than 400000 records. in this table I have one or more fields value null. I want to replace this null value to 0 or any other value.. but when replace with simple command ,"Cntrl+H", it updates only 9000 records. please give me solution for this that I can replace this field with 0 or any other value..

please give me solution if I want to append records of this table to other tables with null value in this field.

thanks in advance

 

[arnelgp]

use update query to do the updating. Create a Query in design view. select the table you want to update. select the fields to be updated. Click on the ribbon, Update (update query).
on UpdateTo field, type:


IIF([field1] Is Null, 0, [field1])


field1 is just a placeholder, put the correct fieldname.
run the query.

 

[CJ_London]

some sql like this

UPDATE myTable set nullsfield=nz(nullsfield,0)



where myTable is the name of the table and nullsfield is the name of the field you want to update. The above assumes nullsfield is numeric. if it is text, replace the 0 with "0"

Note: if this is an indexed field, it is generally better to leave as null as you can set the index to ignore nulls. You can then use the format property to show something if null. e.g.

#;#;#;"Null"

or

#;#;#;"0"

 

[Pat Hartman]

Are you sure that you really want to replace nulls with 0? Zero has a meaning. Null does not. For example, in a test score, 0 means that 0 is the score of the test whereas null means that no score has been entered. Replacing nulls with zeros will also affect averages.

The average of 3,0,3 is 2 but the average of 3, null, 3 is 3

Rather than replacing the nulls with zeros, you probably need to learn how to use them when doing calculations.

 

[ootkhopdi]

Thanks to all ...for their sugesstions...