Code to combine report filter and date range

[rhubarb]

Currently I have a form that I can filter a report from by selecting the item I wish to filter from a list box. This is super basic:

Private Sub cmdPreview_Click()

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

ElseIf Me.lstTicketReport.Selected(1) = True Then
DoCmd.OpenReport "Tickets by Address", acViewPreview




End If
End Sub

That works great! Now separately I was using the code from Method 2 from here http://allenbrowne.com/casu-08.html to filter by date range. Which worked too! But how can I combine both of these so I can select from my list box and if the data range is entered they will filter the report in conjunction?

I'm thinking an AND statement to combine them somehow but am not too sure.

[June7]

Not sure that anything needs to be 'combined'. You have two different reports.

strWHERE = 'code to build the date criteria

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

ElseIf Me.lstTicketReport.Selected(1) = True Then
DoCmd.OpenReport "Tickets by Address", acViewPreview, , strWHERE

End If

Are the values of the list box the exact report names? Then maybe:
strWHERE = 'code
DoCmd.OpenReport Me.lstTicketReport, acViewPreview, , strWHERE

[rhubarb]

So something like...

If Me.lstTicketReport.Selected(0) = True Then
DoCmd.OpenReport "All Tickets Billable", acViewPreview, strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")" And "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"

I get a type mismatch with this but it seems to be close.
So this should pull my "All Tickets Billable" report and filter it by the strDateField.

[rhubarb]

Here I've simplified to see if I can get just the results to include if it's billable AND if it's after the start date.

Dim strDateField As String
Dim strWhere As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strDateField = "[DATE]"

If IsDate(Me.txtStartDate) And Me.lstTicketReport.Selected(0) = True Then
DoCmd.OpenReport "All Tickets Billable", acViewPreview, strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"

This doesn't work at all and only seems to return all the Billable tickets. But I feel oh so close...

[June7]

Can you provide project for analysis? Make copy and remove confidential data, run Compact & Repair, zip if still large, attach to post.

DoCmd.OpenReport "All Tickets Billable", acViewPreview, , "date field name here >= #" & Me.txtStartDate & "#"

[rhubarb]

I've attached a copy of the project.

Just to reiterate: currently the code I have opens reports based on Filter By and Filter Tickets. I'd like to be able to use date ranges in addition to the current filtering.


Thank you for your help!

[June7]

Mechanic and Helper fields are multi-value. I have never used multi-value field and not sure how to use as filter criteria. Have you explored this? Some info can be found at http://office.microsoft.com/en-us/ac...in=HA001233722

Suggested code to use date range and ticket filter:

Dim strWhere As String
Dim strReport As String
If Me.lstTicketReport.Selected(0) = True Then
    strReport = "All Tickets Billable"
    strWhere = "[Billable]=" & Me.lstTicketFilter
ElseIf Me.lstTicketReport.Selected(1) = True Then
    strReport = "Tickets by Address"
    strWhere = "[Job Address]='" & Me.lstTicketFilter & "'"
ElseIf Me.lstTicketReport.Selected(2) = True Then
    strReport = "Tickets By Mechanic"
    strWhere = "[Mechanic]='" & Me.lstTicketFilter & "'"
ElseIf Me.lstTicketReport.Selected(3) = True Then
    strReport = "Tickets By Return To"
    strWhere = "[Return To]='" & Me.lstTicketFilter & "'"
ElseIf Me.lstTicketReport.Selected(4) = True Then
    strReport = "Ticket"
    strWhere = "Ticket=" & Me.lstTicketFilter
End If
If Me.lstTicketReport.Selected(4) = False And IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then
    strWhere = IIf(IsNull(Me.lstTicketFilter), "", strWhere & " AND ")
    strWhere = strWhere & "[DATE] Between #" & Me.txtStartDate & "# And #" & Me.txtEndDate & "#"
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere

[rhubarb]

I have been able to filter the multi-value field using the reports but not in conjunction with the date range filter. I'm thinking it may be easier to provide one or the other. Use the ticket filter or a date range. That way if one is enabled the other will not be. I'm having trouble thinking that this level of granular searching will be required or even used.

I'll give the code you provided a try and post back.

Thanks for helping!

[rhubarb]

Okay what I have decided to do is add a "Filter By" field called Date Range. When the form loads the txtStartdate and txtEnddate are disabled.

So when I select the "Date Range" from the list box I want the txtStartDate and txtEndDate to enable. Then I suppose I should call a function to calculate by date range. I can probably use what I have already.

So I added this code:

Private Sub lstTicketReport_AfterUpdate()

If Me.lstTicketReport.Selected(1) = True Then
Me.txtStartDate.Enabled = True And Me.txtEndDate.Enabled = True
End If

InitFilterItems

End Sub

However, when I highlight the 1st field "Date Range" it does not enable my txt boxes.

Thank you!

[June7]

The Enable method for each control must be on separate lines, not connected with AND.

You don't want to be able to filter by date range and another criteria such as Mechanic?

[rhubarb]

I think that is a bit too much and realistically I don't think it will ever be used.I think if there is an option to filter by date range in addition to the other filters that will be ideal.

This is what I have now:

Private Sub Form_Load()

Me.txtStartDate.Enabled = False
Me.txtEndDate.Enabled = False

End Sub

Private Sub lstTicketReport_AfterUpdate()

If Me.lstTicketReport.Selected(1) = True Then
Me.txtStartDate.Enabled = True
Me.txtEndDate.Enabled = True
End If


InitFilterItems

End Sub

[rhubarb]

Okay I have it working to satisfaction.

I added "Date Range" to Filter By and created a report with the following query under Date property:

Between [forms]![Reports].[txtStartDate] And [Forms]![Reports].[txtEndDate]

I am still trying to only have txtStartDate and txtEndDate enabled when "Date Range" is selected in listbox. This is eluding me for some reason. If anything else is selected from Filter By the Start and End Dates should be grayed out.Thanks for sticking with me on this! I should be able to figure out the rest.

[June7]

Set the date boxes as not enabled in the property dialog.

Put the = False lines at the beginning of the AfterUpdate event instead of Load event. Now if user selects dates then changes mind and selects something else the dates will go back to disabled.

[rhubarb]

Thanks June. So as you said I put:

Private Sub Form_AfterUpdate()

If Me.lstTicketReport.Selected(1) = True Then
Me.txtStartDate.Enabled = True
Me.txtEndDate.Enabled = True
Else
Me.txtStartDate.Enabled = False
Me.txtEndDate.Enabled = False



End Sub

And in the properties boxes of the two txt boxes I set enabled to no.
So the boxes are always grayed out now despite my if statement enabling them if date range is selected in list box. What am I missing?

[June7]

You are using Form_AfterUpdate. I have never used this event. Why are you not still using the lstTicketReport_AfterUpdate event?