Access 2003 Crash on query with odbc linked tables

[jsteph]

I've tried to google this but come up with nothing specific yet. I get the dialog message "Microsoft Access has encountered a problem...blah, blah" with the Send Error Report and Dont Send buttons.

This happens when running a normal Select query (not passthru) using two linked tables (sql-server 2000). The sql is this: (this is not from my production mdb, but it is actual sql that reproduces the problem in a test .mdb):

SELECT table1.code1, table1.code1Descr
FROM table1
WHERE table1.code1 In (select code2 from table2 where nz(amt1,0) =0) AND table1.code1Descr In (Select 'x' from table2)

(The Select 'x' From table2 could be a field or literal--same result)

Table1,2 are just dummy test tables here, with the fields shown.

It seems to have to do when 2 things are present:
1. The NZ() function in an IN (or NOT IN) clause
2. Two separate IN clauses.

If I remove the other IN clause it works, or if I remove the NZ() it works.

I know, as always, there are workarounds, but these querys where this happens are from an Access 97 .mdb that has been in successful production for many years. I finally agreed to upgrade this to Access 2003--I had resisted for years to upgrade first to 2000, then to XP, then to 2003--because of exactly the kind of B-F'n-S that's wasting my time right now--stupid bugs that were introduced into 'upgraded' versions.

Does anyone know of any info on this (ie, it's 'known' by MS and a fix is forthcoming) or it's a 'feature' and won't be fixed. To the best of my knowledge, we are on Automatic Updates for both Windows XP and Office, so I should be at the latest SP for both XP Pro and Access 2003.
Thanks,
--Jim

 

[genomon]

I think for the SQL to work correctly you will need to JOIN the tables in the SQL code.

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[jsteph]

The SQL is correct. But as I'd said, there are workarounds. However the 'table2' in this case has many similar records of 'code2' which may match the criteria, therefore a join would return duplicate records.

To join to a Group By query using 'table 2' or use Distinct might suffice, but I'm really looking for a fix for the bug. Mainly because I tend to favor the In clause in this type of query because it's easier to understand (in my opinion) than having joins to Group-by queries or using Distinct.
--Jim