Filter issue?????

[allie357]

I know the problem is with my Where clause but I am not sure how to fix

it. Here is the error.


Syntax error in date in query expression '([RCName]= 'Athletics' AND
[DeptName] Like '*' AND [Date Entered] Between ## and #9/22/06 >0)'.


Here is the current code:


Private Sub Apply_Filter1_Click()


Dim strRCName As String
Dim strDeptName As String
Dim strDate As String
Dim strWhere As String
Dim strNumber 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 the WHERE clause.
strDate = "[DateEntered] Between #" & Me!txtStartDate & _
"# And #" & Me!txtEndDate & "#"


strNumber = " >= " & Val(Nz(Me.txtnumber, 0))


' Combine criteria strings into a WHERE clause for the filter
strWhere = "[RCName] " & strRCName & " AND [DeptName] " &
strDeptName & " AND " & strDate
' Apply the filter and switch it on


With Reports![rpt_Violations_by_RC_x Violations]
.Filter = strWhere
.FilterOn = True
End With


End Sub



I am new to the filtering the report from a form concept. I used a
filter and not parameters because I wanted the user to be able to
choose to enter all the criteria or just
some of it. Am I approaching this the wrong way???

 

[Duane Hookom]

I believe someone else replied in another news group where you asked the
same question. If the reply was simply "Don't multi-post" then let us know.

 

[allie357]

Yes it was don't multi post. I am sorry about that. I just keep running
into this syntax error and it driving me a little crazy.

I believe someone else replied in another news group where you asked the
same question. If the reply was simply "Don't multi-post" then let us know.

--
Duane Hookom
MS Access MVP

I know the problem is with my Where clause but I am not sure how to fix

it. Here is the error.


Syntax error in date in query expression '([RCName]= 'Athletics' AND
[DeptName] Like '*' AND [Date Entered] Between ## and #9/22/06 >0)'.


Here is the current code:


Private Sub Apply_Filter1_Click()


Dim strRCName As String
Dim strDeptName As String
Dim strDate As String
Dim strWhere As String
Dim strNumber 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 the WHERE clause.
strDate = "[DateEntered] Between #" & Me!txtStartDate & _
"# And #" & Me!txtEndDate & "#"


strNumber = " >= " & Val(Nz(Me.txtnumber, 0))


' Combine criteria strings into a WHERE clause for the filter
strWhere = "[RCName] " & strRCName & " AND [DeptName] " &
strDeptName & " AND " & strDate
' Apply the filter and switch it on


With Reports![rpt_Violations_by_RC_x Violations]
.Filter = strWhere
.FilterOn = True
End With


End Sub



I am new to the filtering the report from a form concept. I used a
filter and not parameters because I wanted the user to be able to
choose to enter all the criteria or just
some of it. Am I approaching this the wrong way???