Not In query (1 Viewer)

[waq963]

Hi, I am trying to do a 'Not In' query in access 2007 but the one below doesn't seem to work. It says that an operator is missing when i use the query builder. When i put it in the code as shown below it does not show anything. If i change the Not to In it shows the correct IN data. Any Ideas? Thanks


strSQL = "SELECT tblQ.[Q No] "
strSQL = strSQL + "FROM tblQ INNER JOIN tblAQ ON tblQ.[Q No] = tblAQ.[Q No] "
strSQL = strSQL + "WHERE (((tblQ.[Q No]) Not In ([tblAQ].[Q No])) AND ((tblAQ.ID)=[cboP])) "
strSQL = strSQL + "ORDER BY tblQ.[Q No];"

 

[HiTechCoach]

This may help:

Surviving Subqueries

 

[waq963]

Thanks for the reply. But i don't see how that can help solve my problem.

 

[unclejoe]

I don't believe "Not In" will work here

strSQL = strSQL + "WHERE (((tblQ.[Q No]) Not In ([tblAQ].[Q No]))

It can be something like this

 strSQL = strSQL + "WHERE (((tblQ.[Q No]) <> ([tblAQ].[Q No]))

If you want to use a subquery,

 "WHERE Not In (Select col1, col2,,, from table where id = id)

Check or refer to the correct syntax at Allen Browne's site.

And this syntax to refers to a control is incorrect.

AND ((tblAQ.ID)=[cboP])) "

It should be something like (assuming numeric)

AND ((tblAQ.ID)= " & Me.cboP & ")) "

 

[waq963]

Thanks for the reply. This code doesn't show any results:

strSQL = strSQL + "WHERE (((tblQ.[Q No]) <> ([tblAQ].[Q No]))
I don't realy understand how the sub query would function.

 

[unclejoe]

if both "Not In" and "<>" is not working for you, try this

strSQL = "SELECT tblQ.[Q No] "
strSQL = strSQL + "FROM tblQ INNER JOIN tblAQ ON tblQ.[Q No] = tblAQ.[Q No] "
strSQL = strSQL + "WHERE  tblAQ.ID = " & Me.cboP & " AND tblQ.[Q No] NOT IN "
strSQL = strSQL + "(SELECT tblAQ.* FROM tblAQ WHERE tblAQ.[Q No] = tblQ.[Q No]) "
strSQL = strSQL + "AND tblAQ.[ID] = tblQ.[ID] "
strSQL = strSQL + "ORDER BY tblQ.[Q No];"

If it still doesn't work, as we can't see your data or tables. You might want to post your sample database here. Some of us might give you a better suggestion.

Thanks for the reply. This code doesn't show any results:
strSQL = strSQL + "WHERE (((tblQ.[Q No]) <> ([tblAQ].[Q No]))
I don't realy understand how the sub query would function.