query criteria - building "In" clause in "hidden" textbox - mult values

[wware]

query criteria - building "In" clause in "hidden" textbox - mult values

I have a report that has an Access query as its record source. I have a form for the user to enter criteria for the query. I have combo boxes that hold query criteria and they all work fine.

I have a list box control with which users can select multiple items. Behind the scenes I am building an "in" clause in a hidden textbox. Then, I thought I could just use this text box in the query as criteria on a certain column.

[Forms]![FileQualityReports]![txtHiddenCriteriaLineOfBusiness]

I know the value in the text box is

In ('AUTO','HOME')

which should return some rows when used as query criteria. I'm afraid the query is evaluating my "in" clause as if it is a literal string. So, I went into query parameters and set the datatype of that parameter to be value, not text. (Actually, I've tried both.) No luck.

I've also tried to have the text box just contain 'AUTO','HOME' and build the rest of the in clause in the criteria grid of the query. Same result.

Can anyone help explain why this isn't working. I've been struggling with it quite a while and I'm really stumped. Any advice appreciated!

 

[MicroE]

Remove the criteria form the query and assemble the where condition in VB code. Then pass the where condition as an argument in the Docmd.Open Report action.
http://www.mvps.org/access/reports/rpt0005.htm

 

[wware]

That was a fine suggestion! I took all the criteria out of the query. I used VB to build a filter for the report I was opening with DoCmd. Works perfectly.

I'm still a little frustrated that I couldn't put my criteria in the query to simplify the testing but I'm not going to complain because I'm happy to have something that works! Thank you very much! Wendy

--------------------------------------------------------
I'll put my code here in case anyone else can learn from it:

'build filtering criteria
Dim stFilter As String

stFilter = stFilter & " reviewer like """ & IIf([Forms]![FileQualityReports]![Comboreviewer].[ListIndex] <> -1, [Forms]![FileQualityReports]![Comboreviewer], "*") & """"
stFilter = stFilter & " and lineofbusiness in (" & [Forms]![FileQualityReports]![txtHiddenCriteriaLineOfBusiness] & ")"

'Open report in preview mode
DoCmd.OpenReport "SubroFileQualityReport", acPreview, , stFilter

 

[MicroE]

You are welcome, glad I could assist.