CODEWITHSUNDEEP
sqlms-access
NewSites
NewSites

Reputation: 1739

MS Access: Error from WHERE condition evaluated on query inside join

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:

enter image description here

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?

Upvotes: 0

Views: 378

Answers (1)

NewSites
NewSites

Reputation: 1739

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:

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.

Upvotes: 1

Related Questions