vba 过滤数据表中的 null、空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5907480/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Filter null, empty values in a datasheet
提问by Ali
In an MS Access datasheet, is it possible to filter those records whose value is null or empty in a specific field in my table?
在 MS Access 数据表中，是否可以过滤表中特定字段中值为 null 或为空的记录？
回答by Mitch Wheat
Yes.
是的。
WHERE myCol IS NULL OR LEN(myCol) = 0
回答by HK1
In your SQL statement:
在您的 SQL 语句中：
WHERE Nz(CompanyName, '') = ''
In your form's filter property:
在表单的过滤器属性中：
Nz(CompanyName, '') = ''
The solution posted by Mitch Wheat will also work. I'm not certain which one will offer the best performance. If you are using Mitch's solution and you are also using other conditions in your Where clause, don't forget to insert parenthesis like this:
Mitch Wheat 发布的解决方案也将起作用。我不确定哪一个会提供最好的性能。如果您正在使用 Mitch 的解决方案并且您还在 Where 子句中使用其他条件，请不要忘记插入这样的括号：
WHERE (myCol IS NULL OR LEN(myCol) = 0) AND myCol2 = True
回答by David-W-Fenton
Using a function in the criterion means you can't use the indexes. @Mitch Wheat's solution will use the index for the Is Nulltest, but not for the Len()test.
在标准中使用函数意味着您不能使用索引。@Mitch Wheat 的解决方案将使用索引进行Is Null测试，但不用于Len()测试。
A sargable WHERE clause would be this:
一个 sargable WHERE 子句是这样的：
WHERE myCol Is Null OR myCol = ""
But I'd recommend turning off "allow zero-length strings" and then you need only do the Null test, which on an indexed field will be extremely fast.
但我建议关闭“允许零长度字符串”，然后您只需要进行空测试，这在索引字段上将非常快。
For what it's worth, I find it extremely annoying that MS changed the defaults for new text fields to have Allow ZLS turned on. It means that I have to change every one of them when I'm using the table designer to create new fields.
对于它的价值，我发现 MS 更改了新文本字段的默认值以启用允许 ZLS 非常烦人。这意味着当我使用表设计器创建新字段时，我必须更改每个字段。
