Query will not read string from "dummy" textbox

[echorley]

I have a two-column list box where the user selects multiple Test Names and Test Measures. Through VBA, I loop through and create a string of the selected items and store into two seperate variables, one for each column. I concatenate with the "In" and some parenthesis to end up with the following:

In(ELA,MEAP,Star Math)
In(DRA, Math, PercentileRank)

I place each of the In statements into two seperate dummy text boxes on the form. Then I point the query criteria to these text boxes.

When I run the query, I get nothing. However, if I copy and paste the In statements above from the text boxes directly into the query criteria, I get the desired results.

I changed the code to create an "Or" statement (e.g. "ELA" OR "MEAP" OR "Star Math"), but still the same issue.

Any ideas why the query will not read from the text boxes on the Form? Thanks!

 

[JHB]

Show the SQL-string.

 

[Cronk]

Try generating a string such as
In("ELA", "MEAP" ,"Star Math")

Use chr(34) to 'generate' the double quotes.

 

[spikepl]

You cannot pass SQL language elements like the IN-clause to an existing query. No matter how much you try with various quotes you wind up with a string.

Only values of parameters can be passed.

You have to construct the query on the fly, or modify the SQL of en existing one using the QueryDef object.

 

[echorley]

Thanks. The QueryDef is probably the route to go.