Using a multiple-selection list box in a query 1

[Chad1984]

I have a form that allows the user to select options for a report. One multiple-selection list box allows the user to select counties- any combination of counties- before clicking up the report that will only include data for those counties.

I had hoped that I could just refer to the field on the form directly in an SQL query- after all, it would just be like a table of values, rather than an individual value, right? Alas, reality and my desires rarely coincide.

I have found two examples:

http://www.utteraccess.com/forums/showflat.php?Cat=&Number=1477463&page=0&view=&sb=5&o=

http://www.fontstuff.com/access/acctut11.htm

However, these use VBA code to make the query. I suppose I could do that, add code that adds the other parts of the query using data from all the other controls in the form and then manually change the query string for the report. Or vice versa, write code that creates text from the listbox and inserts it in a hidden control for a regular query to use. But I'm not fond of those options. IS there a simply way to use this in a regular query, or what's the best option?

 

[TheAceMan1]

How are ya Chad1984 . . .

Since the result will be printing of a report that includes selected counties, VBA would be perfect!

The idea is that the selected counties would set the [blue]Where Clause[/blue] of a [blue]base SQL[/blue]. Something like:

[blue]WHERE [County] = 'Brooklyn' OR [County] = 'Queens' OR [County] = 'Suffolk'[/blue]

As an suggestion . . . a [blue]multi-select Listbox[/blue] would be perfect for this!

[blue]Your Thoughts? . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Chad1984]

Thanks... so I take it your answer is that, of the various options I listed for using a multiple-selection list box as the base for a report... the best way is to use VBA and assemble recordsource string using the listbox values and the values from every other control on the form, then use the VBA code to launch the report and set the recordsource to that string? Ok

 

[TheAceMan1]

Chad1984 . . .

You can also use the criteria in the [blue]4th arguement[/blue] of the [blue]DoCmd.OpenReport[/blue] method (this may be easier for you). As an example assume you have a report whose recordsource is your base query/sql without any criteria. The following code should suite:

[blue]   Dim LBx As ListBox, itm, Criteria As String
   
   Set LBx = Me![purple][b][i]ListboxName[/i][/b][/purple]
   
   For Each itm In LBx.ItemsSelected
      If Criteria <> "" Then
         Criteria = Criteria & " OR ([County] = '" & LBx.Column(1, itm) & "')"
      Else
         Criteria = "([County] = '" & LBx.Column(1, itm) & "')"
      End If
   Next
      
   DoCmd.OpenReport "![purple][b][i]ReportName[/i][/b][/purple]", acViewPreview, , [b]Criteria[/b]

   Set LBx = Nothing[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Chad1984]

Thanks! That was very helpful, the code worked like a charm with very little change. However, now I'm complicating things further! In order to have totals of the data (i.e. all the same data without the county or being grouped by county), I created a separate query that takes all the data from the first query EXCEPT for county and sums the totals for the remaining data- so it shows the totals for each item across all county, instead of for each item by county. That separate query is used for a subreport. The main report is filtered properly; the subreport is not filtered. What's the best way to filter both? Is there a way to make Access realize the data is related and should affect both reports?

Here's the query for the main report (ServiceReportQuery):

SELECT ServiceQuery.Cou_ID, ServiceQuery.Cou_Name, ServiceQuery.Ser_Name, Count(ServiceQuery.Ser_Name) AS CountOfSer_Name
FROM ServiceQuery
GROUP BY ServiceQuery.Cou_ID, ServiceQuery.Cou_Name, ServiceQuery.Ser_Name;

And here's the query for the subreport (ServiceTotalsReportQuery):

SELECT ServiceReportQuery.Ser_Name, sum(ServiceReportQuery.CountOfSer_Name)
FROM ServiceReportQuery
GROUP BY ServiceReportQuery.Ser_Name;

 

[TheAceMan1]

Chad1984 . . .

I'm a little rusty with subreports (havn't had to use them in a few years). You'll get faster resolution if you post in forum703. Be sure to reference this thread by copying/pasting the thread number which is just underneath the thread title . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884: