联合查询抛出“无效使用 null” 例外
我在 Access 中有两个查询。 创建它们都比较麻烦，但在过程结束时它们确实具有相同数量的具有相同数据类型的字段。 他们都独立工作，产生预期的结果。
不幸的是，
SELECT *
FROM [qry vaBaseQuery-S2]
UNION ALL SELECT *
FROM [qry BaseQuery];
抛出两个“无效使用 null”错误，一个接一个。 我之前曾在具有空值的 Access 2000 查询上使用过 union，没有出现任何问题，所以我有点困惑。 谁能建议这里可能发生什么？
可能相关的更多信息：
两个查询中都没有任何空白行
UNION SELECT *（不带 ALL）会抛出相同的错误，但仅一次？！
编辑：
- 使用字段名称而不是 * 没有帮助
Edit2：
- 鉴于查询将是从表单运行的 make table 查询，我只是将其保留为两个单独的查询（一个 make table 和一个追加）和触发器两者依次。 鉴于下面的答案，这听起来比尝试实际找出 Access 反对的内容要省力得多。
I have two queries in Access. Both of them are moderately nasty to create, but at the end of the process they do have the same number of fields with the same data types. They both work independently, producing the expected results.
Unfortunately,
SELECT *
FROM [qry vaBaseQuery-S2]
UNION ALL SELECT *
FROM [qry BaseQuery];
throws two 'Invalid use of null' errors, one after the other. I've used union on Access 2000 queries with null values before without issue, so I'm a bit stumped. Can anyone suggest what might be happening here?
Further information that might be relevant:
Neither query has any blank rows in it
UNION SELECT * (without the ALL) throws the same error but only once?!
Edit:
- Using the field names instead of * doesn't help
Edit2:
- Given the query was going to be a make table query run from a form anyway, I just left it as two separate queries (one make table and one append) and trigger the two in sequence. Given the answers below, that sounds much less effort than trying to actually work out what Access is objecting too.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您很可能在源查询中进行了一些条件 (Iif()) 数据转换（CStr() 或类似操作）。 Access 可能会以不同于联合的方式优化单独的查询； 有时它会以非常奇怪的顺序评估条件部分。
就像下一个过于简单的情况一样：
这可能会抛出“无效使用 null”或不抛出 - 取决于评估顺序。
编辑：忘记写正确的表达式，这不会给出此错误：
You most probably have some conditional (Iif()) data conversions (CStr() or alike) in your source queries. Access may optimize separate queries differently than in union; sometimes it evaluates conditional parts in very weird order.
Like in next oversimplified case:
This may throw "Invalid use of null" or not - depends on evaluation order.
Edit: forgot write correct expression, which doesn't give this error:
Arvo 写道：“有时 [ACE/Jet] 以非常奇怪的顺序评估条件部分”——我可以保证这一点，而不仅仅是在使用 UNION 时。 这是我最近在 SO 上发布的内容，其中仅向查询添加 WHERE 子句会导致引擎以错误的顺序进行评估，从而导致“无效的过程调用”错误，并且我找不到解决方法。
用于解析多行数据的SQL？
我建议你发布来自两个 Query 对象的 SQL 代码。 也许有人可以发现引擎可能存在问题的地方。
Arvo wrote: "sometimes [ACE/Jet] evaluates conditional parts in very weird order" -- I can vouch for that and not just when using UNION. Here's something I posted recently on SO where merely adding a WHERE clause to a query resulted in the engine evaluating in the wrong order causing an 'Invalid procedure call' error and I could not find a way round it.
SQL for parsing multi-line data?
I suggest you post the SQL code from the two Query objects. Perhaps someone can spot something the engine may have problems with.
正如编辑问题中提到的：鉴于查询将是从表单运行的 make table 查询，我只是将其保留为两个单独的查询（一个 make table 和一个追加）并按顺序触发这两个查询。
As mentioned in edited question: Given the query was going to be a make table query run from a form anyway, I just left it as two separate queries (one make table and one append) and trigger the two in sequence.