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

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

Answer 2

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 3

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.

Answer 4

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.

Answer 5

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"));