Query causes Access to crash! Please help! 1

[greg32246]

I am using Access 2003 and SQL Server 2005. Database is broken up into BE/FE.

I have a query that I have simplified in order to find the cause of my problem. I am trying to return two fields from a table that each have as their criteria a subquery which will exclude certain values.

SELECT RCUST.customernumber, RCUST.countrycode
FROM RCUST
WHERE (((RCUST.customernumber) Not In
(SELECT tblExclusions.ValueToExclude
FROM tblExclusions
WHERE (((tblExclusions.ExclusionType)="Customer")))
AND
((RCUST.countrycode) Not In
(SELECT tblExclusions.ValueToExclude
FROM tblExclusions
WHERE (((tblExclusions.ExclusionType)="CountryCode"));
)));

This query causes Access to crash - Access has encountered an error and needs to close.....

I can run the query with either subquery as a criteria, but when I include both subqueries as criteria, Access crashes. Also, I can copy tblExclusions and paste as a local table and then the query runs fine !!!!??????

I started a brand new database and linked the two tables, then created the query in that database and had the same exact problem. So, I don't think it's a corruption, it's just that Access does not like this query.

Can anyone tell me why Access does not like this query and why it works correctly when tblExclusions is a local table instead of a linked SQL Server table?

Thanks.

 

[PWise]

try as left join

SELECT RCUST.customernumber, RCUST.countrycode
FROM RCUST
Left join (SELECT tblExclusions.ValueToExclude 
           FROM tblExclusions 
           WHERE tblExclusions.ExclusionType="Customer"
           )cusexc
on cusexc.ValueToExclude=RCUST.customernumber
Left join (SELECT tblExclusions.ValueToExclude 
           FROM tblExclusions 
           WHERE tblExclusions.ExclusionType="CountryCode"
           )contexc
on contexc.ValueToExclude=RCUST.countrycode
where contexc.ValueToExclude is null 
or cusexc.ValueToExclude is null

 

[greg32246]

That does work, pwise. Thanks.

Any ideas as to why my initial query does not work? It seems to me that my original query is not all that complex. And why would it work when tblExclusions is a local table, but not when it is a linked table?

 

[PHV]

That does work, pwise
Really ?
I'd use this criteria:

where contexc.ValueToExclude is null
[!]AND[/!] cusexc.ValueToExclude is null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWise]

Well Greg what do you want And or Or.

I was just trying to simplify the query without all the prentices )()()()()()()()(

 

[greg32246]

The criteria was intended to be joined with an AND statement.

I noticed the difference in your example, but I did not feel the need to point it out. I really didn't think it was necessary as I understood that your example was merely provided to show me how to restructure the query using LEFT JOINS.

When I said "That works", I meant that by restructuring the query using LEFT JOINS, I was able to get the query to return the results that I wanted without Access crashing.

Thanks again, pwise!