By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,269 Members | 1,332 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,269 IT Pros & Developers. It's quick & easy.

SELECT NOT IN query not working

P: 1
Hello All,

Thank you for your thoughtful consideration.

I am running Access 2000 (9.0.3821 SR-1)

This query works:

Expand|Select|Wrap|Line Numbers
  1. SELECT [id], [name]
  2. FROM table1
  3. 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')
  4. ORDER BY name;
Against these tables:

Expand|Select|Wrap|Line Numbers
  1. table1
  2. id                     AutoNumber (Primary Key)
  3. name               Text
  4. description       Text
  5. date_create      Date/Time (General Date)
  6. date_update     Date/Time (General Date)
Expand|Select|Wrap|Line Numbers
  1. table2
  2. id                           AutoNumber (Primary Key)
  3. case_type_id          Number (Long Integer)
  4. document_type_id   Number (Long Integer)
  5. date_create             Date/Time (General Date)
  6. date_update            Date/Time (General Date)
Expand|Select|Wrap|Line Numbers
  1. table3
  2. id                                       AutoNumber (Primary Key)
  3. case_number                      Text
  4. delay_type_id                      Number (Long Integer)
  5. document_type_id               Number (Long Integer)
  6. other_document_type Id       Number (Long Integer)
  7. event_open_date                 Date/Time (Short Date)
  8. event_close_date                 Date/Time (Short Date)
  9. date_create                         Date/Time (General Date)
  10. date_update                        Date/Time (General Date)
But this query returns nothing:

Expand|Select|Wrap|Line Numbers
  1. SELECT [id], [name]
  2. FROM table4
  3. WHERE case_number='case_number' AND id NOT IN (SELECT other_document_type_id FROM table3 WHERE case_number='case_number')
  4. ORDER BY name;
Against this table and same above:

Expand|Select|Wrap|Line Numbers
  1. table4
  2. id                           AutoNumber (Primary Key)
  3. case_number          Text
  4. name                      Memo
  5. date_create             Date/Time (General Date)
  6. 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.
Jun 19 '07 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 10K+
P: 12,366
When refering to controls on forms, you use:
Jun 19 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.