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
Expand|Select|Wrap|Line Numbers
- If IsNull(Me![cmbStaff]) Then
- DoCmd.OpenReport "TIMESHEET1", acViewPreview
- Else
- DoCmd.OpenReport "TIMESHEET1", acViewPreview, , "TIMESHEET1.Staff='" & Me![cmbStaff] & "'", acWindowNormal
- End If
I tried combining the two with the code below. However, as mentioned before, I keep getting errors.
Expand|Select|Wrap|Line Numbers
- 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