MS Access:在联接内的查询上评估来自WHERE条件的错误 [英] MS Access: Error from WHERE condition evaluated on query inside join

问题描述

当我将以下查询粘贴到Access 2007中的SQL视图中时:

SELECT ID_Entry, DateVal
FROM (SELECT [Query A].ID_Entry, DateVal
     FROM [Query A] INNER JOIN [Query B] 
     ON [Query A].ID_Entry = [Query B].ID_Entry
     )  AS QueryAJoinB
ORDER BY ID_Entry, DateVal;

它返回一个大记录集，其最小值ID_Entry为19.我需要根据字段DateVal的值从该记录集中选择记录. (ID_Entry是表索引.DateVal计算为CDate(CleanRegData(<arguments with data from the current record>)).函数CleanRegData()在VBA中定义.)当我在DateVal上放置WHERE条件时，例如:

SELECT ID_Entry, DateVal
FROM (SELECT [Query A].ID_Entry, DateVal
     FROM [Query A] INNER JOIN [Query B] 
     ON [Query A].ID_Entry = [Query B].ID_Entry
     )  AS QueryAJoinB
WHERE DateVal = Date()
ORDER BY ID_Entry, DateVal;

查询由于CleanRegData()错误而崩溃.调试表明该函数使用ID_Entry = 1记录中的参数调用.该错误是正确的，因为该记录不包含CleanRegData()所需的数据.这就是使用上述查询的原因-选择DateVal可以对其进行评估的记录，然后再尝试对其进行评估.

Query A返回的记录集具有以19开头的ID_Entry值，但是Query B记录集具有以1开头的所有ID_Entry值.应该减少这两个查询的联接记录集仅包含Query A返回的那些记录-可以评估DateVal的记录.

但是，将WHERE条件放在字段DateVal上似乎会使该值针对Query B中的记录进行评估，而该字段的定义无效.如何获得仅在联接的记录集上执行而不在联接内的查询上执行的WHERE条件?

这是上面在设计视图中运行的第二个查询的图片:

单击调试"会将我带到CleanRegData()，其参数值是从ID_Entry = 1的记录获取的，该记录没有CleanRegData()所需的数据，这就是查询崩溃的原因.但是，如果删除DateVal上的条件，查询将返回一个记录集，其中ID_Entry的最小值为19.所以我的问题是，Access如何从该记录中查找ID_Entry = 1的数据?该记录在该查询中不可用.还是我不明白是什么原因导致了这种情况的发生?

解决方案

首先，我要感谢"Damien_The_Unbeliever"，他通过对我的问题的评论引导我理解了问题的根源.像往常一样，一旦您了解了问题，解决方案就在眼前.我不知道他为什么把他的言论发表为评论而不是答案.如果他将其发布为答案，我会接受的，但是由于他未在此处发布我自己的答案，因此人们可以知道问题的解决方案.我知道他已经有超过10万的声誉，所以大概他并不在乎答案.

总结Damien的评论，问题在于Access在子查询的ON子句中错误地并且惊人地将查询的WHERE谓词与联接条件混合在一起！这导致它尝试对未定义该字段的记录求值DateVal，从而使查询崩溃.

以下是有关此问题的参考文献，第一篇是达米安(Damien)提供的，其他参考资料是在我知道要寻找的东西后通过研究发现的.

• 在 Microsoft Connect 上讨论类似的问题: 错误:外部联接表达式被错误检索
• 有关可能与该问题相关的Microsoft文档(我尚未检查是否确实是同一问题): Access 2007-左联接查询将返回#Error而不是Null

在第四个参考文献中，"CWeb"的回答为我提供了如何开发解决方法的线索.我要做的是将WHERE谓词放到iif()内，以再次测试连接条件，这可以防止在错误的记录上评估DateVal.变通方法总是很麻烦，但它确实有效.当我这样做时，我不再需要子查询QueryAJoinB，该子查询的目的是让连接在WHERE评估之前发生，这是没有发生的(这是问题所在).这是现在可以使用的查询:

SELECT ID_Entry, DateVal
FROM [Query A] INNER JOIN [Query B] ON [Query A].ID_Entry = [Query B].ID_Entry
WHERE IIf([Query A].[ID_Entry] = [Query B].[ID_Entry], [DateVal] = Date(), False)
ORDER BY ID_Entry, DateVal;

成功是甜蜜的！再次感谢，达米安.

When I paste the following query into SQL View in Access 2007:

SELECT ID_Entry, DateVal
FROM (SELECT [Query A].ID_Entry, DateVal
     FROM [Query A] INNER JOIN [Query B] 
     ON [Query A].ID_Entry = [Query B].ID_Entry
     )  AS QueryAJoinB
ORDER BY ID_Entry, DateVal;

it returns a large recordset whose smallest value of ID_Entry is 19. I need to select records from that recordset based on the value of the field DateVal. (ID_Entry is a table index. DateVal is calculated as CDate(CleanRegData(<arguments with data from the current record>)). The function CleanRegData() is defined in VBA.) When I put a WHERE condition on DateVal, as such:

SELECT ID_Entry, DateVal
FROM (SELECT [Query A].ID_Entry, DateVal
     FROM [Query A] INNER JOIN [Query B] 
     ON [Query A].ID_Entry = [Query B].ID_Entry
     )  AS QueryAJoinB
WHERE DateVal = Date()
ORDER BY ID_Entry, DateVal;

the query crashes with an error on CleanRegData(). Debugging indicates that the function is called with arguments from the record for ID_Entry = 1. The error is correct because that record does not contain the data needed by CleanRegData(). That is the reason for using the above query -- to select the records for which DateVal can be evaluated before trying to evaluate it.

The recordset returned by Query A has values of ID_Entry that start with 19, but the recordset of Query B has all values of ID_Entry, starting with 1. The join on those two queries is supposed to reduce the recordset to only those records returned by Query A -- which are the records for which DateVal can be evaluated.

But it appears that putting the WHERE condition on field DateVal causes it to be evaluated for records in Query B, where the definition of that field is not valid. How do I get the WHERE condition to be executed only on the recordset of the join, and not on the queries inside the join?

Here is a picture of the second query above running in Design View:

Clicking on "Debug" takes me to CleanRegData() with its argument values obtained from the record for ID_Entry = 1, which does not have the data needed by CleanRegData(), which is why the query crashed. But if I delete the condition on DateVal, the query returns a recordset in the which the lowest value of ID_Entry is 19. So my question is, how is Access finding the data from that record for ID_Entry = 1? That record should not be available in this query. Or what am I not understanding that is making this happen?

解决方案

First, I would like to thank "Damien_The_Unbeliever", who guided me, via his comments on my question, to understanding the source of the problem. As usual, once you understand the problem, the solution is at hand. I don’t know why he posted his remarks as comments instead of as an answer. Had he posted them as an answer, I’d have accepted it, but since he didn't I’m posting my own answer here so people can know the solution to the problem. I see that he already has a reputation of over 100k, so presumably he’s not concerned about the answer points.

Summarizing Damien’s comments, the problem was that Access was incorrectly and amazingly mixing the query's WHERE predicate with the join condition in the ON clause of the subquery! This caused it to attempt to evaluate DateVal on records for which that field is not defined, and thereby crash the query.

Here are references on this problem, the first provided by Damien, the others found with some research once I knew what to look for:

• Discussion of a similar problem on Microsoft Connect: SQL Server should not raise illogical errors
• Outer join analog of this problem documented by Allen Browne: Bug: Outer join expressions retrieved wrongly
• Microsoft documentation of a problem that might be related (I haven't checked to see if it's really the same problem): ACC2000: Outer Join with WHERE Clause Returns Unexpected Records
• StackOverflow discussion of the outer join analog of the problem: Access 2007 - Left Join to a query returns #Error instead of Null

In the fourth reference, the answer by "CWeb" gave me the clue to how to develop a workaround. What I had to do was put the WHERE predicate inside an iif() that tests the join condition again, which prevents DateVal from being evaluated on the wrong records. It’s a bit clugey, as workarounds always are, but it works. When I do that, I no longer need the subquery QueryAJoinB, whose purpose was to get the join to happen before the WHERE is evaluated, which wasn’t happening (which was the problem). Here is the query that works now:

SELECT ID_Entry, DateVal
FROM [Query A] INNER JOIN [Query B] ON [Query A].ID_Entry = [Query B].ID_Entry
WHERE IIf([Query A].[ID_Entry] = [Query B].[ID_Entry], [DateVal] = Date(), False)
ORDER BY ID_Entry, DateVal;

Success is sweet! Thanks again, Damien.

这篇关于MS Access:在联接内的查询上评估来自WHERE条件的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！