I've got a form that I'm trying to use to provide the user the ability to filter down a report. I've got a date field filter (two text boxes) and a combo box for locations. So essentially the user would be able to filter down to a certain date set and/or select a particular location. I have the code inputted into the 'on click' of a button.
My issue is that when I input only dates, the button doesn't open the report at all. I need to have something selected in the combo box in order for it to open. And then when I do and the report opens, the dates filter correctly but the combo box doesn't filter down to the location.
Here's my code:
Code:
Private Sub btnOpenReport_Click()
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Dim strFilter As String
Set frm = Forms!frmSearchTool
strFilter = ""
Const strcJetDate = "\#mm\/dd\/yyyy\#"
strReport = "rptSearchTool"
strDateField = "[SubmissionDate]"
lngView = acViewReport
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
If Len("" & frm!ComboLocation) = 0 Then
Me.Filter = ""
Me.FilterOn = False
Exit Sub
End If
'Location
If Len("" & frm!ComboLocation) > 0 Then
strFilter = "[Location] = '" & frm!ComboLocation & "'"
End If
'Add filter
Me.Filter = strFilter
Me.FilterOn = True
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If
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