Filtering a Report with a list selection

I have reports and I have my list of options...I want to select from the list and have the report run based off my selections. How do I manage that??
Jass SainiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can gather the selected items and create a "WHERE" filter you can use when calling the report. If it's a Text field you can use the IN keyword:

Dim s As String
Dim var as Variant

For each var in YourListbox.ItemsSelected
  s = s & "," & var
next var

s = Left(s, Len(s)-1)

DoCmd.OpenReport "YourReportName", acViewPreview, , "YourFieldName IN (" & s &")"
Jass SainiAuthor Commented:
Hello Scott,

This is my code and I am not sure why it is not working.  It will open my report...but does not filter

Private Sub cmdRunReport_Click()

'On Error GoTo Err_Handler

    Dim strRptName As String, stWhere As String, rpt As Report
    If Me.cboSelectReport > "" Then
'        strRptName = BuildReportName(Me.ucboSelectReport.Column(1))
        strRptName = Me.cboSelectReport.Column(5)
        stWhere = ListBoxSelection(lstEmployeeID, "Analyst") & " AND " & ListBoxSelection(lstOrg, "Org") & " AND " & ListBoxSelection(lstCostCenter, "CostCenter")
        stWhere = stWhere & " AND " & ListBoxSelection(lstFund, "Fund") & " AND " & ListBoxSelection(lstPEC, "PEC")
        Select Case strRptName
            Case "RptFinal_Table Query"
                stWhere = stWhere & " AND FreezeExemptNum > " & Chr(34) & Chr(34)
            Case Else
                If SetRptSource(strRptName, False) = True Then
                    DoCmd.OpenReport strRptName, acPreview, , stWhere
                End If
        End Select
        MsgBox "Please choose a report."
    End If
    Exit Sub

   ' MsgBox "In cmdPreview_Click" & vbCrLf & Err.Number & "--" & Err.Description
    'Resume Exit_Proc

End Sub

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
First: The ONLY time your report would open in the above code would be when your report name is anything other thant "RptFinal_Table Query". Otherwise, your Select Case block would simply add the "FreezeExemptNum" data to the stWhere variable and exit. I don't know if that's what you intend to do, but it doesn't look right.

I also have no idea why your code won't work, since I have no idea what "ListBoxSelection" does, or why you're using it. I don't see anything even remotely resembling what I suggested you do, so I'm not sure where your code is failing.

Can you printout the value of stWhere just before the call to OpenReport? To do that, insert this line just above:

Debug.Print stWhere

This will print the value of stWhere to the Immediate window in the VBA Editor. After you run the routine, open the VBA editor and copy/paste that value here.
Jeffrey CoachmanMIS LiasonCommented:
Please reply to Scot first...

To me, it is not clear (just from the code you posted) exactly what you have there, and why...

It seems to me like you are using multiple listboxes, and using the AND operator to build your selections from each.
For example:
Listbox 1: Analyst="Bob"
ListBox 2: CostCenter=1 your filter would be something like:
Analyst="Bob" AND CostCenter=1 this correct?
If so, then I don't see a need for multiple listboxes, I only see a need for multiple "comboboxes" (since you may only be selecting one value from each combobox, ...else your AND/OR logic might get confusing)

...In any event here is a very simple sample file of both approaches.
Let us know if it helps


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jass SainiAuthor Commented:
Thanks...I just used a different approach!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.