在where语句中无效使用null；没有空值 [英] Invalid use of null in where statement; no null values

问题描述

我有以下问题:

我正在使用两个查询来解析导入的数据，它们都在选择数据 导入的数据集非常复杂，但这会重现错误.

I am using two queries to parse imported data, they are both selecting data The imported dataset is pretty complex, but this reproduces the error.

QueryA:

SELECT CDbl(FieldA) As DblA, Imported.* From Imported WHERE FieldA IS NOT NULL

QueryB:

SELECT * FROM QueryA WHERE DblA > 7 AND DblA < 600

QueryA运行正常，QueryB抛出无效使用null错误

QueryA runs fine, QueryB throws an invalid use of null error

如果我将QueryA的结果插入表中并针对该表运行QueryB，则不会收到该错误，但这是不希望的.

If I insert the results from QueryA into a table and run QueryB against that I do not receive the error, however, this is not desired.

如果我从QueryB中删除WHERE，它将运行正常.

If I remove the WHERE from QueryB it runs fine.

有其他解决方法吗?还是我应该只接受冗余表?

Is there a different workaround for this? Or should I just accept the redundant table?

推荐答案

啊，修复了它，仍然不知道为什么.

Ah, fixed it, still don't know why.

更改的查询A

SELECT CDbl(Nz(FieldA, 999)) As DblA, Imported.* From Imported WHERE FieldA IS NOT NULL AND Nz(FieldA, 999) <> 999

就我的SQL知识而言，这应该没有什么区别，但是确实有什么区别.如果有人可以解释，我会欢迎.

As far as my SQL knowledge goes this shouldn't make any difference, but it does. If someone can explain it I would welcome it.

999而不是0是因为否则我将在其他函数中除以0错误(尽管实际上Access应该不会运行任何功能，因为它已被滤除).

The 999 instead of 0 is because else I would get division by 0 errors in other functions (while really Access shouldn't be running any functions with it since it's filtered out).

这篇关于在where语句中无效使用null；没有空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！