Help with filter issues?

[allie357]

I am new to the filtering the report from a form concept. In my
current
code the report must be open to apply the filter. I would like to
change it so that it runs the filter while the report is closed and
then opens the report. 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???

Also I am getting this error when I enter some of the criteria.

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

 

[Rick Brandt]

I am new to the filtering the report from a form concept. In my
current
code the report must be open to apply the filter. I would like to
change it so that it runs the filter while the report is closed and
then opens the report. 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???

The OpenReport method has an optional WHERE argument that is designed for
exactly this. All you have to do is supply your criteria to that argument
when you open the rpeort.

 

[allie357]

Any suggestions on the syntax error.

The OpenReport method has an optional WHERE argument that is designed for
exactly this. All you have to do is supply your criteria to that argument
when you open the rpeort.