MS Access: How to replace blank (null ) values with 0 for all records?

Question

MS Access: How to replace blank (null ) values with 0 for all records?

I guess it has to be done using SQL. I can use Find and Replace to replace 0 with blank, but not the other way around (won't "find" a blank, even if I enter [Ctrl-Spacebar] which inserts a space.

So I guess I need to do SQL where I find null values for MyField, then replace all of them with 0.

Any help is greatly appreciated.

Thanks, The Find & Replace guy.

Answer 1

Go to the query designer window, switch to SQL mode, and try this:

Update Table Set MyField = 0
Where MyField Is Null; 

Answer 2

UPDATE table SET column=0 WHERE column IS NULL

Answer 3

If you're trying to do this with a query, then here is your answer:

SELECT ISNULL([field], 0) FROM [table]

If you want to replace the actual values in the table, then you'll need to do it this way:

UPDATE [table] SET [FIELD] = 0 WHERE [FIELD] IS NULL

Answer 4

UPDATE YourTable SET MyField = 0 WHERE MyField IS NULL

works in most SQL dialects. I don't use Access, but that should get you started.

Answer 5

Recommmend you use MDBScript to export your entire database to .sql script file, then modify it and execute your .sql script file to re-create a replication of your MS Access database.

Further information: http://www.mdbscript.com/