Multiple Multi Select Opt to filter a report

[Guest]

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

 

[Guest]

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.

 

[Guest]

I wanna say I'm getting somewhere but I'm not sure.... I understand (pretty
sure) of the building of the different criteria, however, when it comes to my
Status, I'm getting a Type Mismatch error. I have it set to go to the error
but in this case it doesn't. The code is basically the same as the rest.
Am I shoving all this in my cmdPreview_Click? Or is some of this going
elsewhere??
Thanks so much for your help so far. =)

 

[Guest]

I don't put the code in the Print Preview command button click event. It is
a Sub of it's own named (cleverly) PrintReport(). In each of the 3 command
buttons (Preview, Print, Excel), I pass the PrintReport sub a value so it
knows what to do.

Private Sub PrintReport(lngView As Long)

Here is the call from the Preview version:
Call PrintReport(acViewPreview)

From the Print version:
Call PrintReport(acViewNormal)

And for the Excel:
Call PrintReport(3)


The in the PrintReport sub, I use the OpenReport method if lngView is less
than 3. 3 is for the Excel version, and I do things a little differently.
Notice that for the report versions, the strWhere is the Where string I built
using the code I sent you earlier.
If lngView < 3 Then
DoCmd.OpenReport strDocName, lngView, , strWhere
Else

 

[Guest]

Thank you so much for your help. I built up the filter string... ok, I re
did everything and then built up the filter string. But because I had one of
my list boxes tied to an option group I had to make the field tied to the
string variable. I finally got it though and it works great!!! Hooray!!!
That report and all it's filters were my biggest pain. Thanks again! =)

 

[Guest]

I am impressed. That is pretty advanced stuff you are working with there.

 

[Guest]

Thank you. I had no choice but to understand it and make it work =) This
project is supposed to be finished as of tomorrow. I think taking a break
and coming back in the morning after not thinking about it, made it a lot
easier. That and I just deleted all the code I had in there and started
fresh so I wouldn't get confused or make mistakes typing over other stuff. I
am trying to learn this vba & sql stuff as fast as possible. I already have
another project.
These boards and people like you help a lot when my books don't answer to my
specific situations.