How to replace blank (null ) values with 0 for all

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.

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

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

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

Edit

ISNULL function was used incorrectly - this modified version uses IIF

SELECT IIF(ISNULL([field]), 0, [field]) 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 3

I would change the SQL statement above to be more generic. Using wildcards is never a bad idea when it comes to mass population to avoid nulls.

Try this:

Update Table Set * = 0 Where * Is Null; 

Answer 4

without 'where's and 'if's ...

Update Table Set MyField = Nz(MyField,0)

Answer 5

UPDATE table SET column=0 WHERE column IS NULL

Answer 6

The following Query also works and you won't need an update query if that's what you'd prefer:

IIF(Column Is Null,0,Column)