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"

 

[ootkhopdi]

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