Report Filter: Using Text Boxes and Combo List

[Marie Gardner]

Hi,

I want to create a form that filters a report that is between a date range, and includes only the data for the item selected in the combo box.

For example, I am creating a time manager report, and want to have a report created that is between Date1 and Date2 and is for Person1. If no person is selected, it runs for everyone. If no date range is selected, it runs for all dates.

I created two command buttons, one is for running the report for the date range, and one is for running the report only for the selected person in the combo box. They both work individually, however I am have trouble combining the two. Whenever I want to combine the two conditions in the conditions section of the OpenCommand, I get errors. I tried combine the two with an &, but get "Runtime Error 3075: syntax error (missing operator) in query expression". When I try combining the two conditions with AND, I get "Runtime Error 13, Type Mismatch".

Below is the code for the two command buttons.

Command Button 1: Date Ranges. I took this from http://allenbrowne.com/casu-08.html , and it works perfectly

Command Button 2: Combo Box. The variables are: Report Name:TIMESHEET1 , Data-Table for report: TIMESHEET1, combobox: cmbStaff

If IsNull(Me![cmbStaff]) Then
  DoCmd.OpenReport "TIMESHEET1", acViewPreview
  Else
DoCmd.OpenReport "TIMESHEET1", acViewPreview, , "TIMESHEET1.Staff='" & Me![cmbStaff] & "'", acWindowNormal
End If
 

The code for Command Button2 works like this: if no-one is selected in the combo box, it creates the form with no filter. If someone is selected, it filters for only instances where that person appears.

I tried combining the two with the code below. However, as mentioned before, I keep getting errors.

Private Sub cmdPreview_Click()
 
'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    'Purpose:       Filter a report to a date range.
    'Documentation: http://allenbrowne.com/casu-08.html
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim strStaff As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
 
    'DO set the values in the next 3 lines.
    strReport = "TIMESHEET1"      'Put your report name in these quotes.
    strDateField = "[Review_Date]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.
 
    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & 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
 
    strStaff = "TIMESHEET1.Staff='" & Me![cmbStaff] & "'"
 
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
 
    'Open the report.
    'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
 
   If IsNull(Me![cmbStaff]) Then
  DoCmd.OpenReport "TIMESHEET1", acViewPreview, , strWhere
  Else
DoCmd.OpenReport "TIMESHEET1", acViewPreview, , strWhere & strStaff, acWindowNormal
End If
 

Any help is greatly appreciated.