vb.net Nz 和 IIF(Is Null) 的替代方案
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18873279/
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
Alternative to Nz and IIF(Is Null)
提问by Benjamin Beaulieu
I have a query in Access in which one of the fields contains a subquery returning the sum of a specific field from a related table. The result of the subquery is used in further calculations in the main query.
我在 Access 中有一个查询，其中一个字段包含一个子查询，该子查询返回相关表中特定字段的总和。子查询的结果用于主查询中的进一步计算。
This is used in a homemade reporting application in VB.Net (C# would be fine) using ADO.Net. Queries are stored in a table in Access to allow us to correct small bugs or adding temporary features related to the data without publishing our application again.
这用于使用 ADO.Net 的 VB.Net（C# 会很好）中的自制报告应用程序。查询存储在 Access 中的一个表中，使我们可以更正小错误或添加与数据相关的临时功能，而无需再次发布我们的应用程序。
The problem is that it's possible that the subquery returns no result (null). Furthermore, a null value used in calculations will also return a null value. Therefore, I need to replace the null value by 0 if that's needed. I added NZ(subquery, 0), but ADO.Net throws the following exception:
问题是子查询可能不返回任何结果（空）。此外，计算中使用的空值也将返回空值。因此，如果需要，我需要将空值替换为 0。我添加了NZ(subquery, 0)，但 ADO.Net 抛出以下异常：
Undefined function 'NZ' in expression
I learned that it's not possible to use a VBA function in ADO.Net since it's part of the Access.Application object.
我了解到不可能在 ADO.Net 中使用 VBA 函数，因为它是 Access.Application 对象的一部分。
I also considered using IIF(subquery Is Null, 0, subquery), but that would force me to duplicate the subquery, and force Access to execute the subquery twice (probably). Since the subquery is rather complex (and so is the main query), I want to avoid that, at least for the sake of readability, if not performance.
我也考虑使用IIF(subquery Is Null, 0, subquery)，但这会迫使我复制子查询，并强制 Access 执行子查询两次（可能）。由于子查询相当复杂（主查询也是如此），我想避免这种情况，至少为了可读性，如果不是性能。
Is there another way to replace a null value by another value, or to make sure my subquery will always return a non-null sum even with no matching record?
是否有另一种方法可以用另一个值替换空值，或者确保即使没有匹配的记录，我的子查询也将始终返回非空和？
回答by Derek Kromm
Sorry, there's no other way. The cleanest method is going to be
抱歉，没有别的办法了。最干净的方法是
Iif(IsNull([Data]), 0, [Data])
NZ is a function specific to the Access code library, so you won't be able to use it.
NZ 是 Access 代码库特有的功能，因此您将无法使用它。
回答by user3371965
Try using ?? like below:
尝试使用 ?? 像下面这样：
double? nullableVar=null;
double nonNullableVar;
nonNullableVar = (nullableVar ?? 0);
回答by Ali ahmadkhani
Try this:
尝试这个：
int? x = null;
// Set y to the value of x if x is NOT null; otherwise,
// if x == null, set y to -1.
int y = x ?? -1;
To learn more, see microsoft help
要了解更多信息，请参阅微软帮助
回答by David
Take a look at my custom function ez: Undefined function 'Nz' in expression. Here it is:
看看我的自定义函数 ez: Undefined function 'Nz' in expression。这里是：
Public Function ez(ByVal vVal1 As Variant, Optional ByVal vVal2 As Variant = 0) As Variant
If VBA.IsNull(vVal1) Or VBA.Len(vVal1) = 0 Then
ez = vVal2
Else
ez = vVal1
End If
End Function
As you can see, it goes a little further than the original function since it not only considers the second value if the first one is null but also if it is empty
如您所见，它比原始函数走得更远，因为它不仅在第一个值为空时考虑第二个值，而且在它为空时也考虑
Also, the default return value is 0 while with Nz is an empty string.
此外，默认返回值为 0，而 Nz 为空字符串。
