How can I send a parameter to a query for an IN clause in Access

Posted on 2011-05-12
Last Modified: 2012-05-11
I have a query in my access database that contains the code.

WHERE ID in ( [ idlist ]  )

When I run this in access and enter one number , e g 5 , it works fine.  When I type in 5,6 I get nothing.  

I want to put this in a program and pass a parameter with one to a lot of numbers in it.  So

1. How do I test using access. ( what should I type into the parameter box that pops up?
2.  Will this work in my program?
Question by:d2fox
    LVL 23

    Expert Comment

    - in Access we need to use INSTR() function for IN() clause. when the parameter box prompt, keyin the values separated by comma :

    SELECT * FROM Table1 WHERE InStr([Enter values separated by commas],[ID])
    LVL 92

    Expert Comment

    by:Patrick Matthews

    That approach may yield false positives.  For example, suppose you passed in "20,30,40" as your parameter value.

    In addition to matching to ID values 20, 30, and 40, it will also match to ID values 2, 3, and 4.

    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    You can do this:

    SELECT Table1.*
    FROM Table1
    WHERE (((Table1.idlist) In ([val1],[val2],[val3],[val4],[val5],[val6])));

    You can enter as many val's as you like ... even skip every other one, any combination ... up to 6 in this example.  So, if you have a finite number of values to input, this is a workaround.

    LVL 23

    Expert Comment

    - thank you Patrick for pointing that issue. even with a tweak by adding (,) to the beginning and end of the parameter not really working as it should. hope other expert can come up with other solutions.
    - if the asker are up to writing a function, he can try the solution by Microsoft here:
    LVL 119

    Expert Comment

    by:Rey Obrero been using this function for this type of query, place this in a regular module

    Function InPar(sFld, Param)
    Dim parArr, j
    parArr = Split(Param, ",")
    For j = 0 To UBound(parArr)
        If Trim(parArr(j)) = Trim(sFld) Then
            InPar = -1
            Exit Function
        End If
    End Function

    then use a query like this

    select * from table
    where InPar([ID],[Enter ID List])=True

    LVL 92

    Expert Comment

    by:Patrick Matthews
    Modifying OP_Zaharin's approach...

    SELECT * 
    FROM Table1 
    WHERE InStr(1, "," & [Enter values separated by commas] & ",", "," & [ID] & ",") 

    Open in new window

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    please see this article to handle delimited lists:
    LVL 30

    Accepted Solution

    Try: query name par_q, table a(f1:number,  f2:Number)

    Private Sub Command7_Click()
        Dim s As String
        s = InputBox("Enter values , delimited")  ' input example 2, 3 = IN clause
        Dim q As QueryDef
        Set q = CurrentDb.QueryDefs("par_q")    ' query variable
        q.sql = "select f1, f2 from a where f1 IN (" & s & ")"    'query sql including where clause
        DoCmd.OpenQuery q.Name
    End Sub

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now