Ok..I have an existing crosstab query, from which I would like to be able to filter for one or more of the records that it is grouped by. I would like to apply this filter from selections in a listbox. Below is the SQL for my query, and below that is the code I’ve got started but dont know where to go next from my listbox selections. I want to filter my crosstab for the location(s) selected in the list box. If I can do this through the query’s design view, that would probably be much easier.
The query’s SQL:
SELECT qryLocationCompany.LOCATION, qryLocationCompany.COMPANY, Val(NZ([QRYLOCBYCO].[MO],0)) AS MO, Val(NZ([QRYLOCBYCO].[ME],0)) AS ME, Val(NZ([QRYLOCBYCO].[NO],0)) AS [NO], Val(NZ([QRYLOCBYCO].[NE],0)) AS NE, Val(NZ([QRYLOCBYCO].[AO],0)) AS AO, Val(NZ([QRYLOCBYCO].[AE],0)) AS AE, Val(NZ([QRYLOCBYCO].[Total Of SSN],0)) AS [Total Of SSN]
FROM qryLocationCompany LEFT JOIN qryLocByCo ON (qryLocationCompany.LOCATION = qryLocByCo.Location) AND (qryLocationCompany.COMPANY = qryLocByCo.Company)
GROUP BY qryLocationCompany.LOCATION, qryLocationCompany.COMPANY, Val(NZ([QRYLOCBYCO].[MO],0)), Val(NZ([QRYLOCBYCO].[ME],0)), Val(NZ([QRYLOCBYCO].[NO],0)), Val(NZ([QRYLOCBYCO].[NE],0)), Val(NZ([QRYLOCBYCO].[AO],0)), Val(NZ([QRYLOCBYCO].[AE],0)), Val(NZ([QRYLOCBYCO].[Total Of SSN],0));
The code I have started:
Private Sub cmdViewReport_Click()
Dim varItem As Variant
Dim strSQL As String
If Me.lstLocations.ItemsSelected.Count = 0 Then
MsgBox “Please select one or more fields.”, vbExclamation
Me.lstLocations.SetFocus
Exit Sub
End If
For Each varItem In Me.lstLocations.ItemsSelected
‘edit SQL to apply Location filter here
‘create SQL of the query
CurrentDb.QueryDefs(“qryReportAll”).SQL = strSQL
DoCmd.OpenQuery “qryReportAll”, acViewNormal, acReadOnly
End Sub
