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

Posted on 2011-05-12
Medium Priority
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

ID: 35751673
- 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 93

Expert Comment

by:Patrick Matthews
ID: 35751723

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
ID: 35751822
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.

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 23

Expert Comment

ID: 35751827
- 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35751855
i.ve 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 93

Expert Comment

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

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

Open in new window

LVL 143

Expert Comment

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

Accepted Solution

hnasr earned 2000 total points
ID: 35753199
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

840 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