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

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?
d2foxAsked:
Who is Participating?
 
hnasrConnect With a Mentor Commented:
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
0
 
OP_ZaharinCommented:
- 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])
0
 
Patrick MatthewsCommented:
OP_Zaharin,

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.

Patrick
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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.

mx
0
 
OP_ZaharinCommented:
- 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:

http://support.microsoft.com/kb/q210530/
0
 
Rey Obrero (Capricorn1)Commented:
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
Next
End Function


then use a query like this

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

0
 
Patrick MatthewsCommented:
Modifying OP_Zaharin's approach...


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

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please see this article to handle delimited lists:
http://www.experts-exchange.com/A_1536.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.