访问 Vba - 查找空值和空白值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1287208/
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
Access Vba - To find null and blank values
提问by tksy
I am trying to find the blank values and null values in a table. I am using Ascto assign the values of the table to a variable and then based on their ASCII values differentiating null and blank. But I am getting "runtime error 94: Invalid use of null"when the code tries to read the ASCII value of a null field.
我正在尝试在表中查找空白值和空值。我使用Asc将表的值分配给一个变量，然后根据它们的 ASCII 值区分空值和空白值。但是"runtime error 94: Invalid use of null"当代码尝试读取空字段的 ASCII 值时，我得到了。
采纳答案by user25095
You can try the following user-defined function to test the table value:
您可以尝试使用以下用户定义函数来测试表值：
Public Function text_test(str_in as Variant) As Long
' given input str_in, return -2 if input is Null,
' -1 if input is zero-length string; otherwise return 0
' use function Nz to test if input is Null and return -2,
' otherwise check non-null value with Len
' and return -1 if it is a 0-length string,
' otherwise return 0 for anything else
text_test = IIf(Nz([str_in], "null") = "null", -2, _
IIf(Len(str_in) = 0, -1, 0))
End Function
In the immediate window run a test with different inputs: ?text_test("fred");text_test("");text_test(Null);text_test(9);text_test(False)
在即时窗口中运行不同输入的测试： ?text_test("fred");text_test("");text_test(Null);text_test(9);text_test(False)
Should return: 0 -1 -2 0 0
应该返回：0 -1 -2 0 0
Note that you cannot use str_in as stringin the function declaration since this will cause the same error you refer to in your question.
请注意，您不能在函数声明中使用str_in 作为字符串，因为这会导致您在问题中提到的相同错误。
回答by David-W-Fenton
When I have to deal with return values that can be either Null or zero-length string, I use a function that converts ZLS to Null:
当我必须处理可以是 Null 或零长度字符串的返回值时，我使用了一个将 ZLS 转换为 Null 的函数：
Public Function varZLStoNull(varInput As Variant) As Variant
If Len(varInput) = 0 Then
varZLStoNull = Null
Else
varZLStoNull = varInput
End If
End Function
This takes advantage of the fact that the VBA Len() function treats a Null and a ZLS exactly the same so that you don't have to handle each case individually.
这利用了 VBA Len() 函数将 Null 和 ZLS 处理为完全相同的事实，因此您不必单独处理每种情况。
However, remember that if you use this in a WHERE clause, you'll be losing performance because it can't use the indexes. Thus, in a WHERE clause, you'd test for IS NULL or =""
但是，请记住，如果您在 WHERE 子句中使用它，您将失去性能，因为它无法使用索引。因此，在 WHERE 子句中，您将测试 IS NULL 或 =""
SELECT MyField
FROM MyTable
WHERE MyField Is Null Or MyField = ""
That will be much more efficient. The varZLSToNull function is most useful when you're appending processed data to a field that has ZLS Allowed set to NO (as it should).
这样效率会高很多。当您将处理后的数据附加到 ZLS Allowed 设置为 NO（应该如此）的字段时，varZLSToNull 函数最有用。
Another thing you should consider is changing your field so that it disallows ZLS, then running a query (using the WHERE clause above without the Is Null) to replace all the ZLS's with Nulls.
您应该考虑的另一件事是更改您的字段，使其禁止 ZLS，然后运行查询（使用上面没有 Is Null 的 WHERE 子句）将所有 ZLS 替换为 Null。
Of course, that assumes that your data is not distinguishing between Null and ZLS as meaning two different things (Null meaning "we haven't recorded any value here" and ZLS meaning "we have recorded an empty value here").
当然，这假设您的数据没有将 Null 和 ZLS 区分为两种不同的含义（Null 表示“我们没有在这里记录任何值”，ZLS 表示“我们在这里记录了一个空值”）。
回答by pjp
I think you should be using IsNull()to decide if a value is null.
我认为你应该IsNull()用来决定一个值是否为空。
https://web.archive.org/web/1/http://articles.techrepublic%2ecom%2ecom/5100-10878_11-5034252.html
https://web.archive.org/web/1/http://articles.techrepublic%2ecom%2ecom/5100-10878_11-5034252.html
回答by pjp
encapsulate your code inside a if statement and compare the string value to vbNullString like this:
将您的代码封装在 if 语句中，并将字符串值与 vbNullString 进行比较，如下所示：
If (Not (<string> = vbNullString) Then
if the string is NOT null execute your original code
如果字符串不为空，则执行您的原始代码
if it is null add an Else block to execute what you need to do if the value is null
如果它为空，则添加一个 Else 块以执行该值为空时需要执行的操作
回答by whistle britches
Yeah, it's an old thread, big deal...
是的，这是一个旧线程，大不了...
This is the most concise way to test a value for Null and zero-length that I've seen:
这是我见过的测试 Null 和零长度值的最简洁的方法：
FinalValue = IIf(Not Len(Nz(Value, "")) = 0, Value, Null)
How it might perform compared to David Fenton's excellent Function above, I do not know. I do know that the one-liner I present here and David's function do almost exactly the same thing. I suspect the one-liner might perform a bit better than a call out to a Function. On the other hand it makes use of an inclusive If, so it may in fact be slower. Who knows?
与上面大卫芬顿的出色功能相比，它的表现如何，我不知道。我确实知道我在这里展示的单行和 David 的功能几乎完全一样。我怀疑单行代码的性能可能比调用函数要好一些。另一方面，它使用了一个包含的 If，所以它实际上可能会更慢。谁知道？
I use it in Class modules, mainly. For example, when creating a record with a DAO Recordset:
我主要在 Class 模块中使用它。例如，当使用 DAO 记录集创建记录时：
With rst
.AddNew
!JobCardID = IIf(Not m_JobCardID = 0, m_JobCardID, Null)
!ConstructionProjectID = IIf(Not m_ConstructionProjectID = 0, m_ConstructionProjectID, Null)
!MajorDisciplineID = IIf(Not m_MajorDisciplineID = 0, m_MajorDisciplineID, Null)
!ActivityDescriptorID = IIf(Not m_ActivityDescriptorID = 0, m_ActivityDescriptorID, Null)
!ActivityStatus = IIf(Not Len(Nz(m_ActivityStatus, "")) = 0, m_ActivityStatus, Null
'etc...
End With
In the above code, ActivityStatus is the relevant String. Note: I never design a database with fields allowing zero-length strings. Repeat: NEVER.
在上面的代码中，ActivityStatus 是相关的 String。注意：我从不设计包含允许零长度字符串的字段的数据库。重复：从不。
