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.

Ungainly

Answer 1

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

Update Table Set MyField = 0
Where MyField Is Null; 

Bandur

The first answer is also correct and would have enabled me to get it, but this one has the added benefit of Access-specific instructions that made it go a little faster. THANKS to all of you for your help, and Happy New Year! – Ungainly

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

Nahshu

ISNULL in the presented form does not exist in MS Access SQL – Gere

@Gere Correct. The 2nd line, using the WHERE [FIELD] IS NULL is the correct usage in MS Access SQL, whether selecting or updating. – Tahiti

Answer 3

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

Update Table Set MyField = Nz(MyField,0)

Sandrocottus

Answer 4

UPDATE table SET column=0 WHERE column IS NULL

Hexyl

Answer 5

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.

Austriahungary

Duplicate answer... but I saw it came in at the exact same date/time as mopoke's.... – Tahiti

Answer 6

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

Doggish

+1 !your answer solves the problem for queries not tables. Additionally, to "clean" a table, you need to create an update query and set the column values like MyField: NZ(MyField,0) RUN! Works for Access2013 – Daciadacie

Answer 7

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)

Salman

Answer 8

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.

Narbonne

Answer 9

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; 

Soldo

Answer 10

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.

Archenemy

But that will not handle the existing values. It only applies to values that will be added after the change. – Scrophulariaceous

Answer 11

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

Sihunn