[sql] How to replace blank (null ) values with 0 for all records?

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.

Better solution is to use NZ (null to zero) function during generating table => NZ([ColumnName]) It comes 0 where is "null" in ColumnName.

I just had this same problem, and I ended up finding the simplest solution which works for my needs. In the table properties, I set the default value to 0 for the fields that I don't want to show nulls. Super easy.

I used a two step process to change rows with "blank" values to "Null" values as place holders.

UPDATE [TableName] SET [TableName].[ColumnName] = "0"
WHERE ((([TableName].[ColumnName])=""));

UPDATE [TableName] SET [TableName].[ColumnName] = "Null"
WHERE ((([TableName].[ColumnName])="0"));

UPDATE table SET column=0 WHERE column IS NULL

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)

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; 

Using find and replace will work if you type "null" in the find and put a zero in the replace...you will be warned that this cannot be undone.

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.

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

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

Update Table Set MyField = Nz(MyField,0)