如何检查 VBA DAO 记录集中的空值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/664162/
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
How can you check for null in a VBA DAO record set?
提问by Tim Visher
I have an optional field in a database that I'm pulling out using a DAO Record Set. I need to check whether or not the field is set before I concatenate it with other fields. So far I have the following code snippet which I've tried with both Isand =(that's the obviously wrong syntax [[Is | =]]) to no avail. It appears that if I use =it will not correctly compare with Nulland if I use Isthen it complains that it's not comparing with an Object.
我在使用 DAO 记录集提取的数据库中有一个可选字段。在将它与其他字段连接之前，我需要检查是否设置了该字段。到目前为止，我已经尝试了以下代码片段，Is并且=（这显然是错误的语法[[Is | =]]）无济于事。看来，如果我使用=它不会正确比较，Null如果我使用Is然后它会抱怨它没有与对象进行比较。
While Not rs.EOF
If rs.Fields("MiddleInitial") [[Is | =]] Null Then thisMiddleInitial = "" Else thisMiddleInitial = rs.Fields("MiddleInitial")
If prettyName(myLastName, myFirstName, myMiddleInitial) = prettyName(rs.Fields("LastName"), rs.Fields("FirstName"), thisMiddleInitial) Then
MsgBox "Yay!"
End If
rs.MoveNext
Wend
If there's a simpler way to do this I'm totally open to it. prettyName takes 3 Strings as parameters and initially I was just trying to pass rs.Fields("MiddleName") directly but it threw up at a Null value. I'd prefer to do something more direct like that but this is the best I could come up with.
如果有更简单的方法可以做到这一点，我完全愿意。PrettyName 需要 3 个字符串作为参数，最初我只是想直接传递 rs.Fields("MiddleName") 但它以 Null 值抛出。我更喜欢做一些更直接的事情，但这是我能想到的最好的方法。
回答by Hamish Smith
How about:
怎么样：
IsNull(rs.Fields("MiddleInitial").Value)
You could also have a look at this articlewhich has some explanation about Null values in Access VBA apps and how to handle them.
您还可以查看这篇文章，其中对 Access VBA 应用程序中的 Null 值以及如何处理它们进行了一些解释。
回答by Fionnuala
For the example you show, Nz would work:
对于您展示的示例， Nz 将起作用：
thisMiddleInitial = Nz(rs!MiddleInitial,"")
Or simply concatenating the string with an empty string:
或者简单地将字符串与空字符串连接起来：
thisMiddleInitial = rs!MiddleInitial & ""
回答by David-W-Fenton
Your question has been answered by Remou, seems to me, but it occurs to me that you may just be trying to get proper concatenation of the name fields. In that case, you could use Mid() and Null propagation in VBA to get the result.
在我看来，您的问题已由 Remou 回答，但我突然想到您可能只是想正确连接名称字段。在这种情况下，您可以在 VBA 中使用 Mid() 和 Null 传播来获得结果。
I don't use separate middle initial fields, so my usual name concatenation formula is:
我不使用单独的中间初始字段，所以我通常的名称连接公式是：
Mid(("12" + LastName) & (", " + FirstName), 3)
The "12" string at the beginning is going to be tossed away if LastName is Not Null and ignored if it isnull, because the + concatenation operator propagates Nulls.
如果 LastName 不是 Null，则开头的“12”字符串将被丢弃，如果它为null，则将被忽略，因为 + 连接运算符会传播 Null。
To extend this to include middle intials would look like this:
要将其扩展为包括中间名缩写，如下所示：
Mid(("12" + LastName) & (", " + FirstName) & (" " + MiddleInitial), 3)
Assuming your UDF is not doing some kind of complicated cleanup of nicknames/abbreviations/etc., this could replace it entirely, seems to me.
假设您的 UDF 没有对昵称/缩写/等进行某种复杂的清理，在我看来，这可以完全取代它。
回答by Tony
If rst.Fields("MiddleInitial").Value = "Null" Then
If rst.Fields("MiddleInitial").Value = "Null" Then
This works for me. I use MS SQL Database.
这对我有用。我使用 MS SQL 数据库。
回答by Scott Langford
I think the NoMatch option might work in this situation:
我认为 NoMatch 选项可能适用于这种情况：
If rs.NoMatch = True Then
如果 rs.NoMatch = True 那么
回答by Billy
I prefer using the below to account for both Null and Empty string values. It's a good check to use you have forms collecting values from users.
我更喜欢使用以下内容来说明 Null 和 Empty 字符串值。使用表单收集用户值是一个很好的检查。
If Trim(rs.Fields("MiddleInitial") & "") = "" then
