IN Clause not working with parameters

[jamesarnall]

Hi all: I am trying to filter a query based on user input from a ListBox. I loop thru the ListBox and build a comma-delimited list of IDs. It takes one parameter, but if there are 2 or more items in the list, it returns nothing. It's not the query, because if I hard code the same comma-delimited list into the SQL query ("...WHERE CategoryID IN (1,2,3)") it works fine.

This is the query:

SELECT tbl_Subsidiary.SubsidiaryName AS Brand, Sum(tbl_Placeview.Duration) AS SumOfDuration
FROM tbl_Subsidiary INNER JOIN tbl_Placeview ON tbl_Subsidiary.SubsidiaryID = tbl_Placeview.SubsidiaryID
WHERE (((tbl_Placeview.AirTime) Between [Forms]![frmTopBrandsByCategory]![txt_DateFrom] And [Forms]![frmTopBrandsByCategory]![txt_DateTo]) AND ((tbl_Placeview.CategoryID) In ([Forms]![frmTopBrandsByCategory]![txt_CategoryList])))
GROUP BY tbl_Subsidiary.SubsidiaryName
ORDER BY Sum(tbl_Placeview.Duration) DESC;

You can see I'm getting the list of "CategoryID" from a text field; after getting my comma-delimited list from the list box, I assign it to a hidden text field. In debug I can see that the list of IDs is built correctly.

Any ideas? I'm pulling out my hair over this!

Thanks in advance,
james

 

[Pat Hartman]

The problem is that SQL sees "1,2,3" as a single string rather than three separate items. If you want to use the In() this way, you'll need to build the entire SQL string in code.

strSQL = "SELECT tbl_Subsidiary.SubsidiaryName AS Brand, Sum(tbl_Placeview.Duration) AS SumOfDuration
FROM tbl_Subsidiary INNER JOIN tbl_Placeview ON tbl_Subsidiary.SubsidiaryID = tbl_Placeview.SubsidiaryID
WHERE (((tbl_Placeview.AirTime) Between [Forms]![frmTopBrandsByCategory]![txt_DateFrom] And [Forms]![frmTopBrandsByCategory]![txt_DateTo]) AND ((tbl_Placeview.CategoryID) In (" & [Forms]![frmTopBrandsByCategory]![txt_CategoryList] & ")))
GROUP BY tbl_Subsidiary.SubsidiaryName
ORDER BY Sum(tbl_Placeview.Duration) DESC;"

 

[jamesarnall]

Not the answer I had hoped for, but useful nonetheless. Thanks.

 

[Jon K]

Instead of using the IN operator and building the SQL statement each time the query is run, you can use the InStr() function in the query.

See the example attached. Note the leading and ending commas in txt_CategoryList.


Edit: See post #11 below for a modified InStr() expression of the query.
.

 

[Pat Hartman]

I tried to use the InStr() method at one point but discovered that there is a problem with it. Unfortunately, I can't remember exactly what the problem was. I think it was with using arguments that had different lengths.

 

[Jon K]

I my example, the "different lengths" problem has been dealt with by the leading and ending commas in the input string and the query.

With the leading and ending commas, the string ,1,2,33, will return exactly 1 2 and 33.
It won't return 1 2 3 and 33.
.

 

[m.shakeebahmed]

smart thinking... thanks Jon

 

[Brianwarnock]

I want to congratulate m.shakeebahmed for tracking this thread down, tried to find info on the forum regarding this issue a short time ago to help another poster and failed.

Thanks all

brian

Edit Ah! as I scrolled through the threads I see he was pointed to it by Jon, still he asked the right question.

 

[m.shakeebahmed]

Brianwarnock !! in simple words..... do you have any better solution than what Jon came forward with ??

 

[Brianwarnock]

No solution what so ever , as i said or implied I was pleased to see this thread as I had not got a solution.

Brian

 

[Jon K]

Would like to modify the InStr() expression in the query a little so that the user can type the parameter values 1,2,33 etc. without the need of typing the leading and ending commas.

SELECT tbl_Placeview.ID, tbl_Placeview.CategoryID
FROM tbl_Placeview
WHERE InStr("," & Forms!frmTopBrandsByCategory!txt_CategoryList & ",", "," & tbl_Placeview.CategoryID & ",")
.

 

[whitet2000]

Thanks for the tips guys...Had the same problem but all good now.
The following worked for me...

SELECT tbl_Placeview.ID, tbl_Placeview.CategoryID
FROM tbl_Placeview
WHERE Instr([CategoryIDs], tbl_Placeview.CategoryID)>0;

When you run this query in Access it prompts you for [CategoryIDs] parameter. Just enter eg: 3,4,5,6 and correctly; records 3 & 4 come back (coz 5 and 6 don't exist).
I can also confirm this works in code if I execute against an ADO Connection object with the following SQL.

categoryIds = "3,4,5,6"
sql = "exec Query1 '" & categoryIds & "'"
set rs = oConn.Execute(sql , nRecsAffected) ' etc etc

NOTE the surrounding quotes (') before sending the parameter to the query.

Cheers
Trav