Filter multiple items with date criteria

[mukraker]

Hello,

I have created a report to filter by [Employee Name] and by [DATE]

When I run the filtered report, the report returns only the Employee Names I have selected from the list box (which is as it is supposed to do) but it returns the Employee Names for all dates in the database instead of the selected data-range. This is except for the last Employee Name in the selection. It only shows up in the correct date range. For instance in the result below. LEAH is the only Employee that filters both name and date correctly. Any help would be greatly appreciated. Thanks.


This is the result from the immediate window:

[EMPLOYEE NAME] Like "JOHN" OR [EMPLOYEE NAME] Like "RONNIE " OR [EMPLOYEE NAME] Like "ZACHARY" OR [EMPLOYEE NAME] Like "JONATHAN " OR [EMPLOYEE NAME] Like "BUSTER " OR [EMPLOYEE NAME] Like "BILL " OR [EMPLOYEE NAME] Like "JASON " OR [EMPLOYEE NAME] Like "JOHNNY" OR [EMPLOYEE NAME] Like "JOSH " OR [EMPLOYEE NAME] Like "LEAH " AND ([DATE] >= #11/01/2010#) AND ([DATE] < #12/01/2010#)

Private Sub cmdPreview_Click()

Dim strReport As String
Dim varItem As Variant
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptReceivingStats"
strDateField = "[DATE]"
lngView = acViewPreview '

For Each varItem In Me!ListFilter.ItemsSelected

strWhere = strWhere & "[EMPLOYEE NAME] Like " & Chr(34) & Me!ListFilter.Column(0, varItem) & Chr(34) & " OR "

Next varItem
On Error Resume Next
strWhere = Left(strWhere, Len(strWhere) - 4)


If IsDate(Me.txtStartDate) Then
strWhere = strWhere & " AND " & "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If


If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

'Open the report.
Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere


Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler


End Sub

 

[pbaldy]

Personally I would build an IN() clause rather than the repetitive OR's, but I suspect your problem is a lack of parentheses. Because of the mix of AND & OR, you need to specify the desired logic. In other words,

A OR B AND C AND D

may be misinterpreted, but

(A OR B) AND (C AND D)

will not. You should explicitly define your desired logic via parentheses.

 

[mukraker]

This took some thinking through. Eventually changed
strWhere = Left(strWhere, Len(strWhere) - 4)

to

strWhere = "(" & Left(strWhere, Len(strWhere) - 4) & ")"

This worked like a charm. It also fixed several filtered reports that have been behaving incorrectly for months.

Thank you so much for you help Paul. You gave me just enough information to figure it out.

I am going to read up on using an ()IN clause. I imagine it is a more elegant solution.

Have a good day.

 

[pbaldy]

Happy to help. I demo building an IN clause here:

http://www.baldyweb.com/multiselect.htm

It will certainly build a shorter string, which can be handy if the user might make so many selections that you hit string length limits.

 

[mukraker]

Thanks for the tip. I am going to try to get it working in one of my forms, then change all of them once I am comfortable with it . I think it will also help with performance as the size of the tables grow.