"Invalid Function Argument" in EXISTS clause + crash (1 Viewer)

[platedslicer]

I need to do a comparison of a complex query and a table, bringing in results from the first in case there are no records in the second to match certain conditions. SQL below.

SELECT Q1.* FROM Query1 Q1 WHERE NOT EXISTS (SELECT T1.ID FROM Table1 T1 LEFT JOIN Table2 T2 ON T2.Code = T1.Status WHERE T1.ID = Q1.ID AND T1.FirmID = Q1.FirmID AND T2.Description <> "Finalized");

I could swear it was working when I got here after lunch but then it started to complain about an "Invalid Function Argument" (at least, that's what it says in Portuguese). There's no way it's a problem of database size exceeding the 2gb limit (it's under 33mb). I checked all the data types and they matched. A bit of debugging told me the problem was in the first and second conditions inside the first EXISTS, so the JOIN was not responsible. I tried changing aliases around in case I was using reserved keywords. No good. As a last ditch effort I figured maybe the "code" primary key in Table1 was complaining about not being used, so I removed that and indexed the ID field. After that the query began crashing Access whenever it was executed

Eventually I quit being stubborn and used a join with a IS NULL clause to achieve the desired result, but this has me bugged. What could be causing the EXISTS to fail? It's probably something silly, but please be gentle, it's my first time here...

 

[vbaInet]

Are you sure you're writing this in Access or is this an Oracle db? You are not using the compulsory AS keyword for the table aliases.

 

[platedslicer]

Thank you for the reply sir. It's Access, I guarantee that.

Never really had a problem with declaring aliases implicitly. Just in case, I went back and put AS in all the right places. Still the same thing. It crashes.

 

[boblarson]

First off, does this part actually work on its own?

SELECT T1.ID FROM Table1 T1 LEFT JOIN Table2 T2 ON T2.Code = T1.Status WHERE T1.ID = Q1.ID AND T1.FirmID = Q1.FirmID AND T2.Description <> "Finalized";

 

[vbaInet]

I would have thought that's compulsory in Access, but I guess not. How many rows and columns are you expecting to pull? And how many rows does the query that boblarson showed you in his post return?

 

[platedslicer]

Thanks again for the replies. vbaInet, Query1 has 20 fields and returns 47 rows with my latest test table. I tried testing it with zero records in the base table but it still crashes whenever I try to run it.

The query provided by boblarson works with hard-coded values for the Query1 fields. I can also JOIN Query1 and Table1 without issue outside EXISTS.

Here's the query I'm using to replace it (works fine):

SELECT Q1A.* FROM Query1 AS Q1A LEFT JOIN

(SELECT Q1B.ID FROM Query1 AS Q1B LEFT JOIN

(SELECT T1.ID, T1.FirmID, T2.Description AS Status FROM Table1 T1 LEFT JOIN Table2 T2 ON T2.Code = T1.Status) AS Q2

ON Q2.ID = Q1B.ID WHERE Q2.FirmID = Q1B.FirmID AND Q1B.Status <> "Finalized") AS Q3

ON Q3.CPF = Q1A.CPF WHERE Q3.ID IS NULL;

Meanwhile, removing the index from Table1's ID field causes the crash to morph back into the "Invalid Function Argument" error.

I figure I must be breaking some internal rule of the EXISTS clause, but I don't know those rules nearly well enough to even take a guess.

EDIT: just to be clear, the translation of the error I'm getting is closer to "Invalid Argument to Function". Maybe that'll make things clearer...

EDIT #2: edited SQL

 

[boblarson]

Well, Exists should return a single True or False. So if it can't do either, I'm guessing that can cause a problem.

 

[vbaInet]

Can we see a stripped down version of your db with the EXISTS query?