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:
Code:
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