在 IIF 语句中计算 NULL 时出现问题 (Access)
记录集中的项目 rstImportData("Flat Size") is = Null
有了这个，给出以下语句：
IIF(IsNull(rstImportData("Flat Size")), Null, cstr(rstImportData("Flat Size")))
Result: Throws error 94: Invalid use of Null
如果我通过在错误比较时删除类型转换来更改语句：
IIF(IsNull(rstImportData("Flat Size")), Null, 0)
Result: Null
它将返回 Null，因为它应该是第一次。如果传入的值应该为空，那么我似乎无法在 IIF 中进行类型转换，即使它通过了 IIF 测试，它仍然尝试在正确和错误的答案上对其进行评估。我像这样使用 IIF 的唯一原因是因为我有一个 25 行比较来将导入中的数据与数据库中的匹配记录进行比较，以查看是否需要将之前的数据附加到历史记录中。
有什么想法吗？导入数据的方式将存在空日期，并且电子表格导入采用字符串格式，我必须将任一侧转换为另一侧以正确比较值，但如果任一侧为空，则会发生此异常:(
编辑 我使用 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
此代码扩展为大约 20 列以在数据库中进行比较。我更愿意将检查作为声明的一部分。我可以想到很多解决方案，但它们涉及添加更多代码。如果真是这样的话，我也不是那么容易屈服的人。
我看到的选项是
- 在比较和使用这些新变量而不是记录集之前创建临时变量来完成工作
- 创建一个对象以将记录传递到预格式化并使用，尽管额外的工作将为每个导入类型提供此功能，因为有是具有相似字段的不同文件，
我在这里寻求想法，并且我愿意接受任何有趣的作品，当我决定如何做时，我正在寻找最可重用的方法。
Item in the recordset 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.
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 :(
EDIT
Example of why I was using IIF (and considering using a universal function)
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.
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
将值更改为字符串的简单方法有很大帮助。诀窍是修剪 NULL 字符串将得到空字符串。然后可以对其进行操作，就好像它不是数据库空一样。
我经常使用以下形式：
无需经历一堆恶作剧就能获得有效的号码。对于这个问题来说，空值是无足轻重的。
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:
To get a valid number without having to go through a bunch of hijinks. The null is a nonentity for this problem.
您所描述的行为是 IIf 在 VBA 下运行的标准方式。 Access 2003 帮助对此的描述如下：
“IIf 始终评估 truepart 和 falsepart，即使它只返回其中一个，因此，您应该注意不良的副作用，例如，如果评估 falsepart 结果。除以零错误，即使 expr 为 True 也会发生错误。”
但是，如果您在查询中使用 IIf 语句，则当 expr 为 truepart 时，计算会在 truepart 后短路>True --- 在这种情况下不会评估 falsepart 。不幸的是，这些信息对您没有用......除非您可以将查询合并到比较中。
我不知道还有什么其他方法可以避免 IIf 的错误。我会尝试将 Excel 数据附加到一个表中，该表的结构与您要比较的表的结构相匹配，从而无需在进行比较的同时进行字符串转换。
The behavior you described is the standard way IIf operates under VBA. This is part of what Access 2003 Help says about it:
"IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True."
However, if you use an IIf statement in a query, evaluation short circuits after truepart when expr is True --- falsepart is not evaluated in that case. Unfortunately this information is not useful for you ... unless you can incorporate a query into your comparison.
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.