Filter by form (Report) problem

A

allie357

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.

Code: 
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

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
 
J

J. Goddard

You are missing = signs after [Rcname], [Deptname], and [countofpolicy].
If [RCName] and [DeptName] are strings, they need to be in quotes:

' 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) & ")

I used single quotes - much easier to read and debug.

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.

Code: 
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

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
Click to expand...
 
A

allie357

I tried plugging you code in but I am still getting a syntax error...



J. Goddard said:
You are missing = signs after [Rcname], [Deptname], and [countofpolicy].
If [RCName] and [DeptName] are strings, they need to be in quotes:

' 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) & ")

I used single quotes - much easier to read and debug.

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.

Code: 
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

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
Click to expand...
Click to expand...
 
D

Duane Hookom

There was an "AND" missing prior to EnteredOn.

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[RCName] ='" & strRCName & "' " & _
" AND [DeptName] = '" & strDeptName & "' " & _
" AND [CountofPolicy] = " & strNumber & _
"AND ([EnteredOn] >= " & Format(Me.txtStartDate, conJetDate) & _
") AND "([EnteredOn] < " & Format(Me.txtEndDate + 1, conJetDate) &
")

--
Duane Hookom
MS Access MVP

allie357 said:
I tried plugging you code in but I am still getting a syntax error...



J. Goddard said:
You are missing = signs after [Rcname], [Deptname], and [countofpolicy].
If [RCName] and [DeptName] are strings, they need to be in quotes:

' 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) & ")

I used single quotes - much easier to read and debug.

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.

Code: 
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

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
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I often stop at the first one or two errors without noticing others...

--
Duane Hookom
MS Access MVP

J. Goddard said:
I've been staring at the screen for too long - how did I miss that?
Sorry!

J.


Duane said:
There was an "AND" missing prior to EnteredOn.

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[RCName] ='" & strRCName & "' " & _
" AND [DeptName] = '" & strDeptName & "' " & _
" AND [CountofPolicy] = " & strNumber & _
"AND ([EnteredOn] >= " & Format(Me.txtStartDate, conJetDate) & _
") AND "([EnteredOn] < " & Format(Me.txtEndDate + 1, conJetDate)
& ")
Click to expand...
Click to expand...
 
A

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
Duane said:
I often stop at the first one or two errors without noticing others...

--
Duane Hookom
MS Access MVP

J. Goddard said:
I've been staring at the screen for too long - how did I miss that?
Sorry!

J.


Duane said:
There was an "AND" missing prior to EnteredOn.

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[RCName] ='" & strRCName & "' " & _
" AND [DeptName] = '" & strDeptName & "' " & _
" AND [CountofPolicy] = " & strNumber & _
"AND ([EnteredOn] >= " & Format(Me.txtStartDate, conJetDate) & _
") AND "([EnteredOn] < " & Format(Me.txtEndDate + 1, conJetDate)
& ")
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I wasn't aware that you could set the filter property on a report that was
already open. I always use criteria in the where condition of
DoCmd.OpenReport or in the report's record source query. My typical code for
doing this is:

Dim strWhere as String
strWhere = "1 = 1 "
If Not IsNull(Me.cboRCName) Then
strWhere = strWhere & " AND RCName Like '*" & _
Me.cboRCName & "*' "
End If
If Not IsNull(Me.cboDeptName) Then
strWhere = strWhere & " AND DeptName = '" & _
Me.cboDeptName & "' "
End If
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND [DateField] >= #" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND [DateField] <= #" & _
Me.txtEndDate & "# "
End If
DoCmd.OpenReport "rptWhatever", acPreview, , strWhere


--
Duane Hookom
MS Access MVP


allie357 said:
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
Duane said:
I often stop at the first one or two errors without noticing others...

--
Duane Hookom
MS Access MVP

J. Goddard said:
I've been staring at the screen for too long - how did I miss that?
Sorry!

J.


Duane Hookom wrote:
There was an "AND" missing prior to EnteredOn.

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[RCName] ='" & strRCName & "' " & _
" AND [DeptName] = '" & strDeptName & "' " & _
" AND [CountofPolicy] = " & strNumber & _
"AND ([EnteredOn] >= " & Format(Me.txtStartDate, conJetDate) &
_
") AND "([EnteredOn] < " & Format(Me.txtEndDate + 1,
conJetDate)
& ")
Click to expand...
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

Filter by form more problems! 0
Syntax error in filter? 1
Filter issue????? 2
Filtering a report issues 1
Dependent combo boxes and filtering a report by a form... 0
Problem with form and print preview of a report 1
Filter Syntax error? 1
Filter report problems... 0

Top