My example was off. But in my real SQL the Subselect returned some NULL vales. So I recoded the Subselect to have a WHERE field1 is not null and the "not in" worked.
>The NOT IN is a part of WHERE clause
>... WHERE table1.field1 NOT IN ...
>>SELECT table1.field1
>>FROM table1 NOT IN
>>(SELECT DISTINCT table2.field1
>>FROM table2);
>>
>>Why does the "NOT IN" not work?
>>
>>Brenda