Report Filter????

A

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???
 
R

Rick Brandt

allie357 said:
Rick said:
allie357 said:
I know the problem is with my Where clause but I am not sure how to
fix [snip]
Click to expand...

http://www.cs.tut.fi/~jkorpela/usenet/xpost.html#why
Click to expand...

Sorry about that. It won't happy again. However, I still need help
with this error...
Click to expand...

I explained in a reply to another post you made that you do not need to
directly manipulate the Report's filter property. All you have to do is
supply the appropriate filter string as the WHERE argument of the OpenReport
method.
 
A

allie357

I understand what you are saying about the OpenReport method, I was
referring to the syntax error that won't let me filter properly.

Rick said:
allie357 said:
Rick said:
allie357 wrote:
I know the problem is with my Where clause but I am not sure how to
fix [snip]

http://www.cs.tut.fi/~jkorpela/usenet/xpost.html#why
Click to expand...

Sorry about that. It won't happy again. However, I still need help
with this error...
Click to expand...

I explained in a reply to another post you made that you do not need to
directly manipulate the Report's filter property. All you have to do is
supply the appropriate filter string as the WHERE argument of the OpenReport
method.
Click to expand...
 
J

John Spencer

I would rewrite the whole thing as follows

Private Sub Apply_Filter1_Click()
Dim strWhere As String
Dim strNumber As String

strWhere = "" 'Initialize the variable

' Build criteria string for RCName field
If IsNull(Me.CboRCName.Value) = False Then
strWhere = " AND RCName = """ & Me.CboRcName & """"
End If

' Build criteria string for DeptName field
If IsNull(Me.cboDeptName.Value) = False Then
strWhere = StrWhere & " AND DeptName =""" & Me.cboDeptName & """"
End If

If IsNull(Me.txtStartDate) = False and Me.txtEndDate = False then
strWhere = strWhere & " AND [DateEntered] Between #" &
Me.txtStartDate & _
"# And #" & Me.txtEndDate & "#"
END IF


'Strip off the leading " AND "
IF Len(strWhere)> 0 then
strWHERE = Mid(StrWhere, 6)
End if

' WHAT IS THIS LINE What is it supposed to do???
'strNumber = " >= " & Val(Nz(Me.txtnumber, 0))

' Check if the report is open.
If SysCmd(acSysCmdGetObjectState, acReport, "rpt_Violations_by_RC_x
Violations") <> acObjStateOpen Then
'Not open so open it
DoCmd.OpenReport "rpt_Violations_by_RC_x
Violations",acViewPreview,,strWhere
Else
'Not sure this will work with an already open report, never tried
doing it this way.
With Reports![rpt_Violations_by_RC_x Violations]
.Filter = strWhere
.FilterOn = True
End With
end if

End Sub

allie357 said:
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???
Click to expand...
 
A

allie357

' WHAT IS THIS LINE What is it supposed to do???
'strNumber = " >= " & Val(Nz(Me.txtnumber, 0))
Click to expand...

This line is supposed to add the >= numeric value, for example, 3. It
allows the user to restrict the report by number of violations.

My original code works with strWhere = "[RCName] " & strRCName & "
AND [DeptName] " & strDeptName & " AND " & strDate

but does not work when I try to add strNumber to the code. I don't
understand why.
Also I would like to change the code so the report does not have to be
open.


John said:
I would rewrite the whole thing as follows

Private Sub Apply_Filter1_Click()
Dim strWhere As String
Dim strNumber As String

strWhere = "" 'Initialize the variable

' Build criteria string for RCName field
If IsNull(Me.CboRCName.Value) = False Then
strWhere = " AND RCName = """ & Me.CboRcName & """"
End If

' Build criteria string for DeptName field
If IsNull(Me.cboDeptName.Value) = False Then
strWhere = StrWhere & " AND DeptName =""" & Me.cboDeptName & """"
End If

If IsNull(Me.txtStartDate) = False and Me.txtEndDate = False then
strWhere = strWhere & " AND [DateEntered] Between #" &
Me.txtStartDate & _
"# And #" & Me.txtEndDate & "#"
END IF


'Strip off the leading " AND "
IF Len(strWhere)> 0 then
strWHERE = Mid(StrWhere, 6)
End if

' WHAT IS THIS LINE What is it supposed to do???
'strNumber = " >= " & Val(Nz(Me.txtnumber, 0))

' Check if the report is open.
If SysCmd(acSysCmdGetObjectState, acReport, "rpt_Violations_by_RC_x
Violations") <> acObjStateOpen Then
'Not open so open it
DoCmd.OpenReport "rpt_Violations_by_RC_x
Violations",acViewPreview,,strWhere
Else
'Not sure this will work with an already open report, never tried
doing it this way.
With Reports![rpt_Violations_by_RC_x Violations]
.Filter = strWhere
.FilterOn = True
End With
end if

End Sub

allie357 said:
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???
Click to expand...
Click to expand...
 
J

John Spencer

Your posted code didn't use strNumber at all. I don't know the name of the
numberfield so I've just used TheNumberField below.

The last section of this code checks to see if the report is open and if it
is not, it opens the report with the filter applied.

Private Sub Apply_Filter1_Click()
Dim strWhere As String

strWhere = "" 'Initialize the variable

' Build criteria string for RCName field
If IsNull(Me.CboRCName.Value) = False Then
strWhere = " AND RCName = """ & Me.CboRcName & """"
End If

' Build criteria string for DeptName field
If IsNull(Me.cboDeptName.Value) = False Then
strWhere = StrWhere & " AND DeptName =""" & Me.cboDeptName & """"
End If

If IsNull(Me.txtStartDate) = False and Me.txtEndDate = False then
strWhere = strWhere & _
" AND [DateEntered] Between #" & Me.txtStartDate & _
"# And #" & Me.txtEndDate & "#"
END IF

If IsNull(Me.TxtNumber)= False then
StrWhere = StrWhere & " AND [TheNumberField] >=" & me.txtNumber
End If

'Strip off the leading " AND "
IF Len(strWhere)> 0 then
strWHERE = Mid(StrWhere, 6)
End if

'=======================================
' Remove this section if you don't want to attempt to modify
' Check if the report is open, if not open it with the filter
If SysCmd(acSysCmdGetObjectState, acReport, _
"rpt_Violations_by_RC_x Violations") <> acObjStateOpen Then
'Not open so open it
DoCmd.OpenReport _
"rpt_Violations_by_RC_x Violations",acViewPreview, , strWhere
Else
'Not sure this will work with an already open report, never tried
doing it this way.
With Reports![rpt_Violations_by_RC_x Violations]
.Filter = strWhere
.FilterOn = True
End With
end if

'=========================================
' replace it with

DoCmd.OpenReport "rpt_Violations_by_RC_x Violations", _
acViewPreview, , strWhere

End Sub

allie357 said:
' WHAT IS THIS LINE What is it supposed to do???
'strNumber = " >= " & Val(Nz(Me.txtnumber, 0))
Click to expand...

This line is supposed to add the >= numeric value, for example, 3. It
allows the user to restrict the report by number of violations.

My original code works with strWhere = "[RCName] " & strRCName & "
AND [DeptName] " & strDeptName & " AND " & strDate

but does not work when I try to add strNumber to the code. I don't
understand why.
Also I would like to change the code so the report does not have to be
open.
Click to expand...
 
A

allie357

I really appreciate all of your guidance. However when I use this code
it fails to filter by date completely and I don't understand why.


John said:
Your posted code didn't use strNumber at all. I don't know the name of the
numberfield so I've just used TheNumberField below.

The last section of this code checks to see if the report is open and if it
is not, it opens the report with the filter applied.

Private Sub Apply_Filter1_Click()
Dim strWhere As String

strWhere = "" 'Initialize the variable

' Build criteria string for RCName field
If IsNull(Me.CboRCName.Value) = False Then
strWhere = " AND RCName = """ & Me.CboRcName & """"
End If

' Build criteria string for DeptName field
If IsNull(Me.cboDeptName.Value) = False Then
strWhere = StrWhere & " AND DeptName =""" & Me.cboDeptName & """"
End If

If IsNull(Me.txtStartDate) = False and Me.txtEndDate = False then
strWhere = strWhere & _
" AND [DateEntered] Between #" & Me.txtStartDate & _
"# And #" & Me.txtEndDate & "#"
END IF

If IsNull(Me.TxtNumber)= False then
StrWhere = StrWhere & " AND [TheNumberField] >=" & me.txtNumber
End If

'Strip off the leading " AND "
IF Len(strWhere)> 0 then
strWHERE = Mid(StrWhere, 6)
End if

'=======================================
' Remove this section if you don't want to attempt to modify
' Check if the report is open, if not open it with the filter
If SysCmd(acSysCmdGetObjectState, acReport, _
"rpt_Violations_by_RC_x Violations") <> acObjStateOpen Then
'Not open so open it
DoCmd.OpenReport _
"rpt_Violations_by_RC_x Violations",acViewPreview, , strWhere
Else
'Not sure this will work with an already open report, never tried
doing it this way.
With Reports![rpt_Violations_by_RC_x Violations]
.Filter = strWhere
.FilterOn = True
End With
end if

'=========================================
' replace it with

DoCmd.OpenReport "rpt_Violations_by_RC_x Violations", _
acViewPreview, , strWhere

End Sub

allie357 said:
' WHAT IS THIS LINE What is it supposed to do???
'strNumber = " >= " & Val(Nz(Me.txtnumber, 0))
Click to expand...

This line is supposed to add the >= numeric value, for example, 3. It
allows the user to restrict the report by number of violations.

My original code works with strWhere = "[RCName] " & strRCName & "
AND [DeptName] " & strDeptName & " AND " & strDate

but does not work when I try to add strNumber to the code. I don't
understand why.
Also I would like to change the code so the report does not have to be
open.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

error 3011 in case 4 only 1
Filter Syntax error? 1
Syntax error in filter? 1
Please help changing the way this report is filtered and opened... 2
Filter issue????? 2
Help with filter issues? 2
Form error? 1
Filtering a report issues 1

Top