Query Criteria

[Ray1127]

I have a form that the user selects which report to run and sets the criteria for the report. Criteria choices are start date, end date, Group and sub group. The issue is that a group can have any number of sub groups and sometimes only specific sub groups are selected. I've used a Listbox to display the subgroups and set the multiselect property to Simple. The problem is that in the query that is the source for the report how do I set the criteria. I've tried forms!frm1!List10. I also tried In forms!frm1!list10 Neither works. How do I use a multi-select listbox in a query to get only those subgroups that were selected?

 

[randy700]

Perhaps a function that creates the WHERE portion of your query from the list box.
Then, put [blue]= FunctionName() [/blue] in the query.


Randy

 

[Ray1127]

I tried that on Monday I can do it If I build the query in VBA but that doesn't work as the query is the source for a report. I guess I'm going to have to use the query in VBA to make a temporary table put the data there and then use that temorary table as the source for the report. Didn't want to do it that way as it adds to the processing time for the report but don't see another way around it.

 

[randy700]

I'm confused.
Why can't you use the function on the query grid?

Randy

 

[Ray1127]

I've tried but the query ends up returning 0 records. If I hard code it with a specific sub group or multiple sub groups it works. I put a breakpoint at the end of the function so I could see exactly what is returned and unless I'm missing something it looks just as it should. Unless I'm missing something. Here's the results -- Ok, it's official I'm an idiot. The string that is returned by FunctionName() should be "choice1","choice2" instead it's coming back choice1,choice2 I forgot the qoutes. Now it works.

Looked at that damn thing for 2 days and didn't notice that. Talk about brain dead.