Report based on a crosstab query

[Guest]

Hi,

I created a report based on a crosstab query and I need to add some
filtering either in the report or in the query but can't find how to do it.
For example, my report contains Site Name, Cycle and Results, when the user
open the report, I would like him to be able to choose a specific Site Name
or all (with a list box that would be even better), I found some ways to do
it but they are not efficient and I need to create multiple queries and
tables. Any idea?

Thank you

 

[Guest]

Have you thought about using a form to enter the parameters and then using
those as criteria in the crosstab query? The criteria would be somthing like
this ---
[Forms]![YourFormName]![YourTextBoxName]

 

[Guest]

I'm assuming Site Name is a row heading in the crosstab query.

Create an unbound form with a multi-select list box, lstSites, of all site
names and a button with code along these lines in its Click event procedure:

Dim varItem As Variant
Dim strSiteList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstSites

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSiteList = strSiteList & ",""" & ctrl.ItemData(varItem) & """"
Next varItem

' remove leading comma
strSiteList = Mid(strSiteList, 2)

strCriteria = "[Site Name] In(" & strSiteList & ")"

DoCmd.OpenReport "YourReportNameGoesHere", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No Sites Selected", vbInformation, "Warning"
End If

The user can select one or more sites from the list, then click the button
to open the report for just that site or sites. Setting the MultiSelect
property of the list box to 'Extended' would probably be best as this would
enable the user to Shift + click to select a contiguous range of sites or
Ctrl + click to select non-contiguous sites. Or you could include a Select
All button with:

Dim n As Integer

For n = 0 To Me.lstSites.ListCount - 1
Me.lstSites.Selected(n) = True
Next n

If you want a Clear Selections button use:

Dim n As Integer

For n = 0 To Me.lstSites.ListCount - 1
Me.lstSites.Selected(n) = False
Next n

Ken Sheridan
Stafford, England