CODEWITHSUNDEEP
sqlms-access-2010
sam
sam

Reputation: 955

Invalid argument to function in access sql WHERE NOT IN

I have a table1

  PreA  BaB   CPS  CommName CommName_CPS
    we   ds   123   Degree   123-Degree
    Se   dd   345   arrow    345-arrow 
    KM   sd   674   inv      674-inv

I have a table2

  PreA  BaB   CPS  CommName CommName_CPS
    we   ds   123   Degree   123-Degree
    Se   dd   345   arrow    345-arrow 
    KM   sd   674   inv      674-inv
    DJ   gfd  435   klm      435-klm
    rt   ggsd 865   hff      865-hff

Table1 (is a query table which is from another table) has field CommName_CPS which is concate two fields,same with table2. I'm trying to sort out uncommon data when compare tbl1 with tbl2 by using below query 

SELECT table1.PreA, table1.BaB,table1.CPS,table1.CommName,table1.CommName_CPS
FROM table1
WHERE CommName_CPS Not In (select CommName_CPS from table2);

I get an error Invalid argument to function.

Upvotes: 0

Views: 683

Answers (2)

dbmitch
dbmitch

Reputation: 5386

As I mentioned in my comments..

Change your Table 1 query to trap for null values when you concatenate the two fields:

SELECT PreA, BaB, CPS,CommName,  
NZ([CPS],"") & "-" & NZ([CommName],"") AS CommName_CPS From <YourOtherTable>

Upvotes: 1

Serge
Serge

Reputation: 2744

Try this one, not exists will be better to use if CommName_CPS could be null, you can read more about it in this article http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

SELECT *
FROM   table1 t1
WHERE  NOT EXISTS (SELECT 1
                   FROM   table2 t2
                   WHERE  t1.CommName_CPS = t2.CommName_CPS)

Upvotes: 1

Related Questions