如何用0替换所有记录的空白(null)值?

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

MS Access:如何将所有记录的空(空)值替换为0 ?

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.

我想必须使用SQL完成。我可以使用Find和Replace将0替换为blank，但不能用另一种方式替换(即使我输入[ctrl -空格键]插入空格，也不会“查找”空格。

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

我想我需要用SQL来找到MyField的空值，然后用0替换它们。

11 个解决方案

#1

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

转到查询设计器窗口，切换到SQL模式，并尝试以下操作:

Update Table Set MyField = 0
Where MyField Is Null;

#2

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

如果你想用一个查询来实现这一点，那么以下就是你的答案:

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

从[表]中选择ISNULL([字段]，0)

Edit

编辑

ISNULL function was used incorrectly - this modified version uses IIF

ISNULL函数使用不正确——这个修改后的版本使用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

#3

UPDATE table SET column=0 WHERE column IS NULL

#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.

适用于大多数SQL方言。我不使用Access，但这应该能让你开始。

#5

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

没有“where’s and”if’s…

Update Table Set MyField = Nz(MyField,0)

#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)

#7

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

更好的解决方案是在生成表=> NZ([ColumnName])时使用NZ(null到0)函数，它在ColumnName中为null。

#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.

如果在find中输入“null”，在replace中输入一个0，那么使用find和replace将有效。你会被警告，这是无法挽回的。

#9

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.

我只是遇到了同样的问题，最后我找到了最简单的解决方案。在表属性中，我将不希望显示null的字段的默认值设置为0。超级简单。

#10

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

我使用了两步过程，将“blank”值的行更改为“Null”值作为位置占位符。

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

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

#11

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.

我将把上面的SQL语句改为更通用的。使用通配符对于大量人群来说并不是一个坏主意，以避免null值。

Try this:

试试这个:

Update Table Set * = 0 Where * Is Null;

#1