IN and NOT IN

[ashooo]

Hi,

I'm having a bit of trouble with a SELECT query.

I'm trying to select a count of the number of products which are not part of Invoices, Sales Orders etc.

The code I have so far is....

SELECT COUNT(*) FROM tbProdDesc WHERE
    [Stock Code] NOT IN 
    (SELECT [Stock Code] FROM tbQuotePart UNION 
     SELECT [Stock Code] FROM tbInvoicePart UNION
     SELECT [Stock Code] FROM tbSOPart UNION
     SELECT [Stock Code] FROM tbBIPart UNION
     SELECT [Stock Code] FROM tbPOPart)

However this brings back 0, when it should be over 28000.

If i change it to In, instead of NOT IN i.e.

SELECT COUNT(*) FROM tbProdDesc WHERE
    [Stock Code] IN 
    (SELECT [Stock Code] FROM tbQuotePart UNION 
     SELECT [Stock Code] FROM tbInvoicePart UNION
     SELECT [Stock Code] FROM tbSOPart UNION
     SELECT [Stock Code] FROM tbBIPart UNION
     SELECT [Stock Code] FROM tbPOPart)

This brings back 700 odd records which is correct.

For some reason it does not like my NOT IN command.

Any help is much apprecitaed.

Thanks