RE: Multiple Multi Select Opt to filter a report

This may appear daunting at first, but here is a way you can do it. I have a
report in one of my apps that uses 6 mulit select list boxes the user may
choose items from, an Option group to select the report format, and 3 command
buttons (preview, print, export to Excel)

The idea is to create a Where string that will include all the selected
items for all the list boxes where the user made a selection.

Here is an example of the first two:

'Billable Product Offering
strWhere = BuildWhereCondition("lstBillProdOffering")
strFieldName = "quniBPOReportsPVA.ProjectID "
If Len(strWhere) > 0 Then
strWhere = strFieldName & strWhere
End If
'Master Activity
strWhereNext = BuildWhereCondition("lstMActivity")
strFieldName = "tblMasterActivity.MActivity "
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND " & strFieldName & strWhereNext
Else
strWhere = strFieldName & strWhereNext
End If
End If
*********************************
Notice that strWhere is being construted one list box at a time. Only if
there were selections made in the list box is it added to the Where string.
Here is the function BuildWhere that creates the string:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function
*********************
The function above first decides how to structure the condition, then reads
through the ItemsSelected collection and creates the string.

The option group for formatting gets very complicated, so don't worry about
it now. It is designed to change the sorting and grouping of the report
programmatically, but if the above doesn't frighten you, the sorting and
grouping code certainly will :)

See if you can make this make sense. Post back if you have questions.
--
Dave Hargis, Microsoft Access MVP


"Pixie78" wrote:

I need serious help. I have been trying to make this report filter for what
seems like forever.

I have a huge query, since I couldn't figure out how to filter both a form
and subform and read it wasn't a great way to go. The query is all the
information for my employees and all the information on training they have
taken.
I have two different reports that need to be opened. One from each view
point.
1. Employee and all the classes they took (EmpRegReport)
2. Class and all the Employees that took it (CourseRegReport)

I have a form frmTracker that has all the options I need on it. However
there are a lot of options. The first Option group decides which report will
open and filter.

The second option group is connected with a multi select list box. To
choose if the report will be filtered by Employee (then select which
employees), Department (then select which departments) and so on.

The third list box is the status of the class the employee took. Whether it
was completed, incomplete, missed.....

There is also a date range I need to enter but I haven't gotten that far
down the form yet.

I can make the first two work fine but as soon as I try to filter on with
the status, I get errors or type mismatch or it just won't accept the filter.

Any information would be GREATLY appreciated. Or if there is an
easier/better way to get what I'm trying to do, I would love to hear it.
Thanks so much.


Sample of code (there are 8 cases and basically they look the same, save the
data pulled...name, department, shift....)

Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click


Dim strDoc As String
Dim varItem As Variant
Dim strCriteria As String
Dim strStatus As String
Dim strFilter As String


Set db = CurrentDb()
Set qdf = db.QueryDefs("qryRegistration")


'Build Criteria for View Info Frame
Select Case Me.ViewInfoFrame.Value
Case 1
strDoc = "RegistrationReport"
Case 2
strDoc = "CourseMainRprt"
End Select

'Build Criteria for Frame80
Select Case Me!Frame80
Case 1
For Each varItem In Me!FilterList.ItemsSelected
strCriteria = strCriteria & "'" &
Me!FilterList.ItemData(varItem) & "',"
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "Please Make a Selection" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Left(strCriteria, Len(strCriteria) - 1)
strCriteria = "SELECT * FROM qryRegQuery WHERE
qryRegQuery.EmpName IN(" & strCriteria & ") ORDER BY qryRegQuery.EmpName;"

End Select

With Me.lstStatus
For Each varItem In Me!lstStatus.ItemsSelected
strStatus = strStatus & "'" & Me!lstStatus.ItemData(varItem) &
"',"
Next varItem
If Len(strStatus) = 0 Then
MsgBox "Please Make a Selection" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strStatus = Left(strStatus, Len(strStatus) - 1)
strStatus = "SELECT * FROM qryRegQuery WHERE qryRegQuery.Status
IN(" & strStatus & ");"
End With

qdf.SQL = " & strCriteria & " And " & strStatus & "

DoCmd.OpenForm strDoc, acPreview, , qdf.SQL
Set db = Nothing
Set qdf = Nothing
.

Loading