vba 在 IIF 语句 (Access) 中评估 NULL 时出现问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2747940/
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
Problem evaluating NULL in an IIF statement (Access)
提问by Mohgeroth
Item in the recordset rstImportData("Flat Size") is = Null
记录集中的项目 rstImportData("Flat Size") is = Null
With that, given the following statement:
有了这个，给出以下声明：
IIF(IsNull(rstImportData("Flat Size")), Null, cstr(rstImportData("Flat Size")))
Result: Throws error 94: Invalid use of Null
If I change the statement by removing the type conversion upon a false comparison:
如果我通过在错误比较时删除类型转换来更改语句：
IIF(IsNull(rstImportData("Flat Size")), Null, 0)
Result: Null
It returns Null as it should have the first time. It appears that I cannot do a type conversion in an IIF if the value passed in should ever be null even if it passes an IIF test, it still attempts to evaluate it at both the true and false answer. The only reason I'm using IIF like this is because I have a 25 line comparison to compare data from an Import against a matching record in a database to see if I need to append the prior to history.
它返回 Null，因为它应该是第一次。如果传入的值应该为空，即使它通过 IIF 测试，我似乎也无法在 IIF 中进行类型转换，它仍然尝试在正确和错误的答案中对其进行评估。我像这样使用 IIF 的唯一原因是因为我有一个 25 行比较来将来自导入的数据与数据库中的匹配记录进行比较，以查看是否需要附加之前的历史记录。
Any thoughts? The way data is imported there will be null dates and where the spreadsheet import is in a string format I must convert either side to the other to compare the values properly but if either side is null this exception occurs :(
有什么想法吗？导入数据的方式将有空日期，并且电子表格导入为字符串格式，我必须将任一侧转换为另一侧以正确比较值，但如果任一侧为空，则会发生此异常:(
EDITExample of why I was using IIF (and considering using a universal function)
编辑我为什么使用 IIF 的示例（并考虑使用通用函数）
If master("one") <> import("one") Or _
master("two") <> import("two") Or _
master("date") <> import("date") Or _ //import("date") comes from a spreadsheet, it comes in as string, CAN be a null value
master("qty") <> import("qty") Or _ //import("qty") comes from spreadsheet, comes in as a string can CAN be null
master("etc") <> import("etc") Then
....stuff....
End If
This code expands for roughly 20 columns to compare in the database. I would prefer to check as part of the statement. I can think of a bunch of solutions but they involve adding much more code. If that is the case power to it, however I'm not one to give in so easily.
此代码扩展为大约 20 列以在数据库中进行比较。我更愿意检查作为声明的一部分。我可以想到一堆解决方案，但它们涉及添加更多代码。如果这是它的力量，但是我不会那么容易屈服。
Options I see are
我看到的选项是
- Creating temp vars to do the work prior to comparing and using these new vars instead of the recordset
- Creating an object to pass the record into to preformat and work with, though extra work would provide this functionality to each import type since there are different files with similar fields
- 创建临时变量以在比较和使用这些新变量而不是记录集之前完成工作
- 创建一个对象以将记录传递给预格式化并使用，但额外的工作将为每个导入类型提供此功能，因为存在具有相似字段的不同文件
I'm here for ideas, and I'm open to any interesting pieces that can be thrown my way as I get to decide how to do it I'm looking for the most reusable approach.
我在这里寻找想法，我愿意接受任何可以抛出的有趣片段，因为我要决定如何去做，我正在寻找最可重用的方法。
采纳答案by Dave
The simple expedient of changing the value to a string helps tremendously. The trick is that trimming a string which is NULL will get a null string. Which can then be operated on as if it wasn't a database null.
I frequently use the form:
将值更改为字符串的简单权宜之计非常有用。诀窍是修剪一个 NULL 的字符串将得到一个空字符串。然后可以对其进行操作，就好像它不是数据库空值一样。
我经常使用以下表格：
CInt("0" & Trim(SomeVariant & " "))
To get a valid number without having to go through a bunch of hijinks. The null is a nonentity for this problem.
获得一个有效的号码，而不必经历一堆骗局。对于这个问题，null 是一个非实体。
回答by HansUp
The behavior you described is the standard way IIf operates under VBA. This is part of what Access 2003 Help says about it:
您描述的行为是 IIf 在 VBA 下运行的标准方式。这是 Access 2003 帮助说明的一部分：
"IIfalways evaluates both truepartand falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepartresults in a division by zero error, an error occurs even if expris True."
" IIf总是同时计算truepart和falsepart，即使它只返回其中之一。因此，您应该注意不良副作用。例如，如果计算falsepart导致除以零错误，即使expr也会发生错误是真的。”
However, if you use an IIf statement in a query, evaluation short circuits after truepartwhen expris True--- falsepartis not evaluated in that case. Unfortunately this information is not useful for you ... unless you can incorporate a query into your comparison.
但是，如果您在查询中使用 IIf 语句，则当expr为True时，在truepart之后评估短路---在这种情况下不评估falsepart。不幸的是，这些信息对您没有用……除非您可以将查询合并到您的比较中。
I don't know of any other way to avoid your error with IIf. I would try appending the Excel data into a table whose structure matches that of the table you will compare against, thereby eliminating the need to do a string conversion at the same time you do the comparison.
我不知道还有什么其他方法可以避免 IIf 的错误。我会尝试将 Excel 数据附加到一个表中，该表的结构与您要比较的表的结构相匹配，从而无需在进行比较的同时进行字符串转换。
