Are you IN or are you out? (Not IN)

This is a part one of two-part on how you can make your queries much more powerful. In first part, we look at whether we’re in or not. Part two can be found here.

The IN clause is a great tool to have in your arsenal, it can allow your forms to be editable or filter forms using .

MS Access Report Generator

What is the IN clause?

The IN clause is a member of the SQL language syntax and can be used in your WHERE clause to specify which data to include or exclude. (There are other uses too, as discussed here).

Two Syntax Alternatives

You can use the IN clause by specifying the criteria to include in your query. For example, let’s say you want a list of all orders in IL, WI and MN. You could do it this way:

The problem with the above statement is adding a ton of states makes for a long SQL Statement. Instead you can use the IN clause as a shortcut for the equivalent expression above:

Power Up

But what if you don’t want orders from those states? You can combine IN with NOT like this:

Super Charge

The above examples are great ways to use the IN clause, but you can Super Charge your code by using a select statement instead of listing values like this:

Notice how I dropped the INNER JOIN from my SELECT statement and instead I’m using the IN clause to retrieve all orders for those customers who belong in the states in question.

Use IN to query on tables not in the select statement

There are many situations where you need to avoid a JOIN on your select, here are two:

  • You need users to edit data in your form and using a JOIN will make it read only.
  • One or more tables are not stored together. For example: the Order table might be on SQL Server and the Customer table stored locally in Access. Such a join would be slow and not optimal.

Use IN to filter your form

One of my favorite techniques is to filter a form using the WHERE clause of the DoCmd.OpenForm statement. For example, the other day I was building a dashboard for a banking client that included a count of delinquent loans in the system. I made the number clickable, allowing users to launch the frmLoans form with the list of delinquent loans with the following line:

Note that the data is contained in a SQL Server view that’s linked in Access front-end. Instead of joining the view which may cause sub-optimal performance, the IN clause ensure that the list of LoanIDs is evaluated once.

Use IN to avoid duplicates in your queries that has a GROUP BY clause

Many times you will have a query that contains a GROUP BY clause for which you need unique records, but you are getting multiple records due to joins with other tables. Consider using the IN clause instead of joining on one or more tables.

So are you IN or are you OUT?

Stay tuned for the part two!


About the Author:

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.


  1. Courtney August 8, 2018 at 2:49 pm - Reply

    What if you need to provide a listbox, let’s say of people? So you have projects, and multiple people can be assigned to them. Project 1 has person a, b, and c assigned. Project 2 has person a, c and e assigned. Project 3 has person a, b, f assigned.

    Now I want to get a list of all projects where persons a AND c are both assigned. This should return project 1 and 2, but not 3. How would I build that?

    • Bruce September 14, 2018 at 3:59 pm - Reply

      I’m assuming you have a table or query that returns project assignments having a project field (1,2,3) and an assignee field (a,b,c, etc.) in it to start with. Let’s assume it’s called project_assignment. You want to see the intersection of the set of projects with “a” assigned and the set of projects with “c” assigned. To list only those projects with both a and c assigned you could use the following select statement:

      select project_assignment.project
      from project_assignment
      inner join
      (select project from project_assignment where assignee = “c”) as c_list
      on project_assignment.project = c_list.project
      where project_assignment.assignee = “a”

  2. Ron Seelig May 23, 2018 at 7:10 pm - Reply

    George, I’m finding the exact opposite to be true is my case. If I use SELECT x,y,z, FROM “RemoteTable” WHERE “RemoteTable.Field IN (‘a,’b,’c…2000 items) I get a lightning fast response. Howevr if I use SELECT x,y,z, FROM “RemoteTable” WHERE “RemoteTable.Field IN (SELECT field FROM “LocalTable”) the performance is terrible.

    I’m looking for a way to populate inside the parentheses IN () from a list with code.

    Juan Soto mentions
    DoCmd.OpenForm _
    FormName:=”frmLoans”, _
    WhereCondition:=”LoanID IN(SELECT LoanID FROM vw_DelinquentLoans)”

    He also mentions how this helps performance.

    Can anyone clarify how to populate the IN() with code?

  3. Frank R. Ruperto December 23, 2013 at 9:40 pm - Reply

    IN/NOT IN clauses are very useful for evaluating more than one value, and against another table via a subquery. However, the performance cost is similar to using the OR clause.

    • Ben Clothier December 30, 2013 at 9:09 pm - Reply

      Frank, that’s because the IN() is actually a compile-time shortcut for the ORs. Internally, the engine will expand a “x IN(a,b,c)” into a series of “x = a OR x = b OR x = c” . That is why IN([A_comma_delimited_list_of_values_typed_in_a_parameter]) will never work because the parameter is evaluated as a single value, and not parsed by the engine when executing into individual values and evaluated separately. Thus, we have to advise people to use VBA to construct the string dynamically before actually executing it as a SQL like this: “SELECT * FROM y WHERE x IN(” & myVBAVariableContainingCommaDelimitedList & “);”.

      Thanks for commenting!

      • Anthony Griggs July 10, 2018 at 8:08 am - Reply

        Thanks for that Ben, as I was actually trying to do a parameterized “IN” Statement thinking it should work and just began researching it when I came across this.

  4. Guy Van Dyck September 28, 2013 at 5:33 pm - Reply

    My experiences are that NOT IN is very slow in most cases.

    • Guy Van Dyck September 28, 2013 at 5:35 pm - Reply

      You can use a LEFT JOIN and do test WHERE ISNULL(Fieldname of right table) instead of NOT IN

      • Isaac September 28, 2013 at 9:52 pm - Reply


        Wrong syntax for jet SQL. Isnull is for vba or other rdbms.

  5. Isaac Pisors September 25, 2013 at 11:58 pm - Reply

    The best thing about IN() (IN my opinion) rather than a straight join is that in many cases I use them in pass through queries, so my access database has a PT query that runs sql server sql scripting. As you know, it’s not possible to join to (or refer in any way to) a table that’s not on the server.

    Therefore, when coding for this scenario, I have a saved pass through query with all of the SQL correct, except a dummy placeholder for In, like WHERE [SOMEFIELD] IN ‘PIZZA’

    When I run my VBA script, I:
    1. declare string var to hold sql
    2. populate it with the saved query’s querydef object’s .SQL
    3. use Replace() to replace dummy ‘pizza’ with a long IN() (sometimes containing hundreds and hundreds of values from a form or recordset or whatever the particular process requires)
    4. then run the query.

    In() is beautiful in this scenario and works well with VBA scripting in an access database. The manipulation-abilities of vba…..added to SQL server scripting = Best product!

  6. Anders Ebro September 23, 2013 at 7:18 pm - Reply

    I was about to ask the same question as George has, as to what impacts using In() and (Not In) has, especially on large recordsets.

    Also, I get told the video is not available (Could be a regional thing)

    • Juan Soto September 23, 2013 at 10:19 pm - Reply

      I understand all of your concerns, frankly, if a join works for me than that’s what I would use first. Otherwise I will use the IN clause as needed.

      Glad you liked the post!

  7. George Hepworth September 23, 2013 at 5:41 am - Reply

    While some of the uses you list for the In() clause make sense, I wonder what impacts you’ve seen on performance with this approach. It’s been my experience that In(), and especially Not In() are less efficient than a straight join.

  8. Ruud Jeursen September 23, 2013 at 4:18 am - Reply

    Thanks Juan, great post. Consider me all IN!! 😉

Leave A Comment


Contact Us
close slider
  • This field is for validation purposes and should be left unchanged.