NOT IN SQL (ms access 2013)

is it valid to use a comma-separated set of integers in the IN clause? (or just strings in quotes?)

it depends on the data type of the field in your SQL statement that using the not in clause.

how's your SQL looks like?

the data type is integer. see sql above (select fields form table where ID NOT IN (1,2,3)

the data type is integer

this should work

select fields form table where ID NOT IN (1,2,3)

Here the problem is not with with IN and NOT IN clause , The problem is NULLs in the subselect

Pls try ..

select fields form table 
where 
NOT EXISTS ( SELECT NULL FROM table WHERE ID NOT IN (1,2,3) and ID IS NOT NULL )

Select allOpen in new window

that's what I thought. but it doesn't. Hence the post.

I was wondering about the null. let me try that and get back to you.

I tried the following but now get no data?
SELECT tmpINProducts.*
FROM tmpINProducts
where NOT EXISTS ( SELECT NULL FROM tmpINProducts WHERE prodCustID NOT IN (1,2,3) and prodCustID IS NOT NULL )
ORDER BY prodCustID;

here is my demo

Products table

ID	PrdName	CustID
1	Mango	1
2	Pencil	
3	Chair	2
4	Mousepad	0
5	Keyboard	3

SELECT *
  FROM Products
 WHERE CustId is null or CustID not in (0,1,2)

ID	PrdName	CustID
2	Pencil	
5	Keyboard	3

SELECT *
  FROM Products
 WHERE CustID not in (0,1,2)

ID	PrdName	CustID
5	Keyboard	3

SELECT *
 FROM Products
 WHERE CustID in (0,1)

ID	PrdName	CustID
1	Mango	1
4	Mousepad	0

Select allOpen in new window

it is very basic thing... no magic here

probably you have a query like

select * from Products where CustId not in (CustList)

Select allOpen in new window

and when it runs, it asks for CustID and you enter "0,1,2"

then query becomes

select * from Products where CustId not in ("0,1,2")

Select allOpen in new window

and returns nothing!!!... no customer with CustID = "0,1,2", but 0 or 1 or 2 etc...

Pls try --

SELECT a.*
FROM tmpINProducts a
where NOT EXISTS ( SELECT NULL FROM tmpINProducts b WHERE prodCustID NOT IN (1,2,3) and prodCustID IS NOT NULL 
AND a.prodCustID  = b.prodCustID 
)
ORDER BY prodCustID;

Select allOpen in new window

or

SELECT a.*
FROM tmpINProducts a
where NOT EXISTS ( SELECT NULL FROM tmpINProducts b WHERE prodCustID IN (1,2,3) and prodCustID IS NOT NULL 
AND a.prodCustID  = b.prodCustID 
)
ORDER BY prodCustID;

Select allOpen in new window

Have you tried this-

SELECT a.*
FROM tmpINProducts a
where NOT EXISTS ( SELECT NULL FROM tmpINProducts b WHERE prodCustID IN (1,2,3) and prodCustID IS NOT NULL 
AND a.prodCustID  = b.prodCustID 
)
ORDER BY prodCustID;

Select allOpen in new window

Thanks Folks.
Pat: not sure what the full query looks like
Pawan: seems to work great -will report back.

Hi Pawan: I integrated the where-clause of your suggestion into the full query but got lost on the aliases and the join.

SELECT tmpINProducts.*, tmpItemDies.ProdID, ('P:\AA Pressed Metal\Order Entry\Product Pictures\' & Left(tmpINProducts.[ProdID], 3) & '\' & tmpINProducts.[ProdID] & '.jpg') AS tmpPath
FROM tmpINProducts INNER JOIN tmpItemDies ON tmpINProducts.ProdID = tmpItemDies.ProdID
WHERE NOT EXISTS ( SELECT NULL FROM tmpINProducts b WHERE prodCustID IN (0, 1,2,3) and prodCustID IS NOT NULL
AND a.prodCustID  = b.prodCustID)
ORDER BY prodCustID;

Thanks Kurt,
unfortunately we have lost all Pawan's NULL-handling.
His was very close so will wait for him to hopefully add the finishing touches.

what you should do is this

create excel and put this, so everybody knows what you have, what is needed...

EE-HowToAsk.xlsx

Thanks Pawan, that seems to work. I'll check more closely tomorrow
Thanks also to all who participated