Report Filtering

[rhubarb]

Currently I have a form that allows the user to enter a date range and it will return a ticket report based on that range once the preview button is pushed. If the date range is blank it returns all possible tickets from the report. This works.

I would like to add functionality to this filtering so I added a list box with other options such as address, employee, ticket, etc. Below that I added a combo box that, based on list box selection, displays further detail. (If address is selected then the combo box displays all possible addresses).

I have one report that lists all possible information and I would like to reuse this for all variations of reports if possible.

If I can get the basic filtering working I would like to have the capability to filter by date and an item in the list box, ie. an employee between the inputted date range.



Any help is appreciated.

[June7]

Either have the report RecordSource refer to the report controls for input parameters or pass filter criteria to the report through code (macro or VBA) when the report opens.

In an event, such as button Click, execute a macro or VBA with command to open report with criteria. In VBA would look like:
DoCmd.OpenReport "report name", , , "fieldname1=" & Me.textbox1 & " AND fieldname2='" & Me.textbox2 & "' AND fieldname3=#" & Me.textbox3 & "#"

Example shows use of delimiters: apostrophe for text, # for dates

[rhubarb]

Thanks for the reply. This is steering me in the right direction.

So far I am leaving the date range alone and just trying to open up reports based on my list and combo box.

I have:
lstTicketReport = ListBox
This has six static filtering fields (Billable, Employee, Ticket, etc)

lstTicketFilter = ComboBox
This opens up possible values from the above selection. (If Employee is selected the combo box shows all possible employees)

I am a complete beginner when it comes to code so I'm sure it could be more efficient.

Private Sub cmdPreview_Click()


If Me.lstTicketReport.Selected(0) = True Then
DoCmd.OpenReport "All Tickets Billable", acViewPreview

ElseIf Me.lstTicketReport.Selected(3) = True Then
DoCmd.OpenReport "All Tickets Non-Billable", acViewPreview

ElseIf Me.lstTicketReport.Selected(5) = True Then

End If

End Sub

Now first I think it's ridiculous to statically address the items in the list box. It's only possible here because I only have 6 items to filter the reports by. I think a loop would be better but I'm not sure how to code it.

Which leads to how do I code the combo box? I've selected (5) above which happens to be tickets so the combo box drops down with all possible tickets. How can I track the tickets so when I click preview it brings up the right one selected. This is where I thought the loop would be necessary because there could be hundreds of tickets. So perhaps it would be a loop within a loop?

Thanks!

[June7]

Going by the code example you show, this might be possible:

DoCmd.OpenReport "All Tickets " & Me.lstTicketReport, acViewPreview

But this will show all tickets. If you want to filter to specific criteria (employeeID, ticketID, etc), don't open report from the listbox event. Use it to requery the combobox. The combobox AfterUpdate event would then open the report.

DoCmd.OpenReport "All Tickets " & Me.lstTicketReport, acViewPreview, , filter criteria string here

Constructing the filter criteria string is the trick. If you include another criteria, such as date range, that will be entered in textboxes, use a button click to execute the OpenReport action.

This can get quite complicated.

Check out tutorials here http://www.datapigtechnologies.com/AccessMain.htm

Especially:
the 3 about comboboxes under Access Forms:Control Basics
and
Build a Custom Filter in Your Form under Access Forms:Tips and Techniques

[rhubarb]

Okay so from that tutorial I was able to see how to add the criteria in the record source of report that points to my lstTicketFilter. Great tutorials!

Thanks so much for the help!