如何检查 VBA DAO 记录集中是否为空？
我在数据库中有一个可选字段，我正在使用 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
如果有更简单的方法来做到这一点，我完全愿意。 PrettyName 接受 3 个字符串作为参数，最初我只是尝试直接传递 rs.Fields("MiddleName") 但它抛出了 Null 值。 我更愿意做一些更直接的事情，但这是我能想到的最好的办法。
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 Is and = (that's the obviously wrong syntax [[Is | =]]) to no avail. It appears that if I use = it will not correctly compare with Null and if I use Is then it complains that it's not comparing with an Object.
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
怎么样：
您还可以查看 这篇文章 其中对 Access VBA 应用程序中的 Null 值以及如何处理它们进行了一些说明。
How about:
You could also have a look at this article which has some explanation about Null values in Access VBA apps and how to handle them.
对于您显示的示例，Nz 可以工作：
或者简单地将字符串与空字符串连接起来：
For the example you show, Nz would work:
Or simply concatenating the string with an empty string:
在我看来，雷莫已经回答了你的问题，但我突然想到你可能只是想正确连接名称字段。 在这种情况下，您可以在 VBA 中使用 Mid() 和 Null 传播来获取结果。
我不使用单独的中间名首字母字段，因此我常用的名称连接公式是：
如果 LastName 不为空，则开头的“12”字符串将被丢弃；如果它为 null，则开头的字符串将被丢弃，因为 + 串联运算符会传播 Null。
要扩展它以包括中间缩写，看起来像这样：
假设您的 UDF 没有对昵称/缩写/等进行某种复杂的清理，在我看来，这可以完全取代它。
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.
I don't use separate middle initial fields, so my usual name concatenation formula is:
The "12" string at the beginning is going to be tossed away if LastName is Not Null and ignored if it is null, because the + concatenation operator propagates Nulls.
To extend this to include middle intials would look like this:
Assuming your UDF is not doing some kind of complicated cleanup of nicknames/abbreviations/etc., this could replace it entirely, seems to me.
If rst.Fields("MiddleInitial").Value = "Null" then这对我有用。 我使用 MS SQL 数据库。
If rst.Fields("MiddleInitial").Value = "Null" ThenThis works for me. I use MS SQL Database.
我认为 NoMatch 选项可能在这种情况下起作用：
If rs.NoMatch = True then
I think the NoMatch option might work in this situation:
If rs.NoMatch = True Then
我更喜欢使用下面的内容来解释 Null 和 Empty 字符串值。 这是一个很好的检查，可以使用从用户那里收集值的表单。
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.