Query excluding matching records not working

[Trauton]

Hi,

The query below selects the records existing in Table A which have no
matches in Table B. I use this query every month without problem. This
month however when I run the query get the following error mesage: "Invalid
Operation". I checked in the Help button of the error message and got Error
3219.

SELECT qryTableA.ProjectID, qryTableA.Customer, qryTableA.DealDescription,
qryTableA.User_Name, qryTableA.DateBDCompleted, qryTableA.Status,
qryTableA.OppType
FROM qryTableA LEFT JOIN qryTableB ON qryTableA.ProjectID =
qryTableB.Deals.PROJECTID
WHERE (((qryTableB.Deals.PROJECTID) Is Null));

I have checked that all the individual queries run fine and I can even
select the matching records between the two queries, however when I try to do
the left join I get the error message listed above.

I would REALLY appreciate any suggestions.

Regards,

Trauton

 

[Allen Browne]

We don't know what's in the underlying queries that may be affecting this,
but here's a couple of thoughts.

1 What is qryTableB.Deals.PROJECTID?
Is there really a dot in one of those names? If ProjectID is a field name
that appears more than once in qryTableB, and you are trying to specify the
one from the Deals table, could you either drop the other field out of the
query (it doesn't seem necessary if they are both the same value), or else
alias the field, e.g.:
SELECT Deals.PROJECTID As TheProjectID
and then in the query you showed us:
ON qryTableA.ProjectID = qryTableB.TheProjectID
etc.

2. References
Open a code window (e.g. press Ctrl+G).
Choose References on the Tools menu.
Are any references marked "MISSING"?
If so, see:
http://allenbrowne.com/ser-38.html
Messed up references can affect function calls in queries.

3. Name AutoCorrect
If anything has been renamed, Access can get confused about what the names
refer to. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

4. Bad index
Compact the database:
Tools | Database Utilities | Compact/Repair
(You need to do this after turning off Name AutoCorrupt anyway.)

5. JET bug
If the problem persists after this, the error may be one of the many bugs in
JET. (Joint Engine Technology = the data/query engine in Access.) There's a
list of some of them here:
http://allenbrowne.com/tips.html#Bug Engine
See if any of those apply, such as the problems with yes/no fields since
your query uses outer joins.