how to run a sql string built in form in a query

[bbxrider]

from this post, http://access.mvps.org/access/forms/frm0007.htm
I can build the sql string with the selected items from the listbox right in the form that has the listbox
but not sure how to pass that select statement into a query which will be record source for a report


I know I could have a hidden text box and populate the textbox with the sql string and then reference the textbox content in a say a query column criteria row but what do I put in the top box of the query column?
or just how to get that sql string as the sql string for a query.

[June7]

I suggest the easiest approach is to apply the filter string constructed by the looping on listbox to report or form when opening.

DoCmd.OpenReport "report name", , , strWHERE

[bbxrider]

ok so don't bother with the query, just go right to the report, yes? In your example the strWhere last parameter of invoking the report is essentially the same as 'building' the sql as the 'record source' of the report, yes?

[June7]

In my suggestion, the only part of the sql that is built is the WHERE clause, only without the WHERE keyword. It does not alter the report RecordSource, it sets report Filter.

[bbxrider]

? not following your reply, I am building a complete sql query 'where' clause in a form where the 'or' criteria are the items selected in a listbox, "select * from myTable m where m.someField = 'listboxItem1' or m.someField = 'listboxItem2' "etc
that sql query will be the record source for a report, I thought I would have to 'run' the sql in a query that is the record source for the report. I was asking how to 'pass' the sql statement to a access query which would run it. you have lost me now talking about the sql without the 'where' as a filter in the report. why would there be a need for filter?

[June7]

Filter criteria is what you are building with the code that loops through the listbox.

You can try the code referenced in the link you posted but I see a flaw in that code. It first sets the variable strSQL to the text "SELECT * FROM Employees where [EmpID]=" then in the loop it concatenates that variable to each value in the listbox. This means with each iteration of the loop the text "SELECT * FROM Employees where [EmpID]=" will be repeated and the result will be a bad SQL statement.

Try it, see what the constructed value looks like by printing it to the VBA immediate window: Debug.Print strSQL

Correcting that code would mean putting the loop first to build the criteria then outside the loop concatenate once to the end of "SELECT * FROM Employees where [EmpID]="

My suggestion just builds the WHERE clause of an SQL statement. If the fields of the report don't need to change why bother programmatically modifying the RecordSource? Just apply filter to the report.

I never use dynamic parameterized queries as a RecordSource, only static queries.

So with your example, the report RecordSource would be: SELECT * FROM myTable;

Then the code would build the WHERE criteria and pass to the report when it opens with code in post 2.

I think use of IN operator is a better option with multiple parameters for same field. Review: http://www.allenbrowne.com/ser-50.html