Filter by form more problems!

[allie357]

Okay I had to take out the date and number information to make it work
but I need to include that....can anyone tell me what is wrong with the

original code and this code.
See the trick is i have dependent combo boxes and I need to be able to
leave one blank to filter as well. The fiter works with the code below
but not if I leave the DeptName combo blank... I have no idea how to
fix it.

Private Sub Apply_Filter1_Click()


Dim strRCName As String
Dim strDeptName As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rpt_Violations_by_RC_x

Violations") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for RCName field
If IsNull(Me.CboRCName.Value) Then
strRCName = "Like '*'"
Else
strRCName = "='" & Me.CboRCName.Value & "'"
End If
' Build criteria string for DeptName field
If IsNull(Me.cboDeptName.Value) Then
strDeptName = "Like '*'"
Else
strDeptName = "='" & Me.cboDeptName.Value & "'"
End If
' Combine criteria strings into a WHERE clause for the filter
strFilter = "[RCName] " & strRCName & " AND [DeptName] " &
strDeptName
' Apply the filter and switch it on
With Reports![rpt_Violations_by_RC_x Violations]
.Filter = strFilter
.FilterOn = True
End With


End Sub


Private Sub CboRCName_AfterUpdate()
Me!cboDeptName.Requery
End Sub


Private Sub CboRCName_Change()
'Set value of combo box equal to an empty string


Me!cboDeptName.Value = ""
End Sub


Private Sub Form_Open(Cancel As Integer)
Me!CboRCName.Value = ""
Me!cboDeptName.Value = ""
End Sub

Below I am posting my code for a form that should help me filter a
report. I am getting a syntax error here:

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[RCName] " & strRCName & " AND [DeptName] " & strDeptName

& " AND [CountofPolicy] " & strNumber & "([EnteredOn] >= " &
Format(Me.txtStartDate, conJetDate) & ") AND "([EnteredOn] < " &
Format(Me.txtEndDate + 1, conJetDate) & ")


The filter needs to take a number from the count of Policy field and
return results >= the number entered into the field and the start date
and end date need to evaluate the DateEntered field. I am sort of new
to the filter concept in VBA so I need help.

Option Compare Database
Option Explicit


Private Sub CboRCName_AfterUpdate()
Me!cboDeptName.Requery
End Sub


Private Sub CboRCName_Change()
'Set value of combo box equal to an empty string


Me!cboDeptName.Value = ""
End Sub

The original code is as follows:

Private Sub FilterReport1_Click()
Dim strRCName As String
Dim strDeptName As String
Dim strStartDate As String
Dim strEndDate As String
Const conJetDate = "\#mm\/dd\/yyyy\#"
'The format expected for dates in a JET query string.
Dim strNumber As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rpt_Violations_by_RC_x

Violations") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for RCName field
If IsNull(Me.CboRCName.Value) Then
strRCName = "Like '*'"
Else
strRCName = "='" & Me.CboRCName.Value & "'"
End If
' Build criteria string for DeptName field
If IsNull(Me.cboDeptName.Value) Then
strDeptName = "Like '*'"
Else
strDeptName = "='" & Me.cboDeptName.Value & "'"
End If
' Build criteria string for StartDate field
If IsNull(Me.txtStartDate.Value) Then
strStartDate = "Like '*'"
Else
strStartDate = "='" & Me.txtStartDate.Value & "'"
End If
' Build criteria string for EndDate field
If IsNull(Me.txtEndDate.Value) Then
strEndDate = "Like '*'"
Else
strEndDate = "='" & Me.txtEndDate.Value & "'"
End If
' Build criteria string for txtNumber field
If IsNull(Me.txtnumber.Value) Then
strNumber = "Like '*'"
Else
strNumber = ">='" & Me.txtnumber.Value & "'"
End If


' Combine criteria strings into a WHERE clause for the filter
strFilter = "[RCName] " & strRCName & " AND [DeptName] " & strDeptName

& " AND [CountofPolicy] " & strNumber & "([EnteredOn] >= " &
Format(Me.txtStartDate, conJetDate) & ") AND "([EnteredOn] < " &
Format(Me.txtEndDate + 1, conJetDate) & ")
' Apply the filter and switch it on
With Reports![rpt_Violations_by_RC_x Violations]
.Filter = strFilter
.FilterOn = True
End With
End Sub


Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![rpt_Violations_by_RC_x Violations].FilterOn = False
End Sub