SELECT NOT IN query not working

[plaforest]

Hello All,

Thank you for your thoughtful consideration.

I am running Access 2000 (9.0.3821 SR-1)

This query works:

SELECT [id], [name]
FROM table1
WHERE id NOT IN (SELECT document_type_id FROM table2 WHERE case_type_id=case_type_id ) AND id NOT IN (SELECT document_type_id FROM table3 WHERE case_number='case_number')
ORDER BY name;

Against these tables:

table1
id                     AutoNumber (Primary Key)
name               Text
description       Text
date_create      Date/Time (General Date)
date_update     Date/Time (General Date)

table2
id                           AutoNumber (Primary Key)
case_type_id          Number (Long Integer)
document_type_id   Number (Long Integer)
date_create             Date/Time (General Date)
date_update            Date/Time (General Date)

table3
id                                       AutoNumber (Primary Key)
case_number                      Text
delay_type_id                      Number (Long Integer)
document_type_id               Number (Long Integer)
other_document_type Id       Number (Long Integer)
event_open_date                 Date/Time (Short Date)
event_close_date                 Date/Time (Short Date)
date_create                         Date/Time (General Date)
date_update                        Date/Time (General Date)

But this query returns nothing:

SELECT [id], [name]
FROM table4
WHERE case_number='case_number' AND id NOT IN (SELECT other_document_type_id FROM table3 WHERE case_number='case_number')
ORDER BY name;

Against this table and same above:

table4
id                           AutoNumber (Primary Key)
case_number          Text
name                      Memo
date_create             Date/Time (General Date)
date_update            Date/Time (General Date)

The syntax between the two queries is almost identical and there aren't many
differences between the tables.

There are 3 records in table4 for a case_number and only 1 is in table3, so
I would expect a return of two records. I get nothing.

If I remove the NOT IN clause it returns all three records from table4.

If I change it to IN I get the 1 record that is also in table3.

This is very strange.

case_type and case_number are values taken from an active form.

I am unable to find discussion regarding SELECT queries with NOT IN clauses.

Thank you for any help.

[Rabbit]

When refering to controls on forms, you use:
Forms![FormName]![ControlName]