Report Filter Problem

[George]

Good Night,



I have a report called "ExamTimeTablebydate" which I am trying to make dynamic by creating a form with 2 combo boxes that will supply the criteria that changes the form. The combo boxes are "cboOffice" and "cboDepartment". There is a button called "cmdApplyFilter" that triggers the query on the form.
The fields in "ExamTimeTablebydate" which are associated with the query are "Class" and "Venue".
The problem is that when values are placed in the combo box and the "cmdApplyFilter" button is press, the report comes up empty, but when the combo boxes are emptied the table is once again fully loaded with values.

Is there a plausible explanation for this?

Here is my code:

Option Compare Database
Private Sub Form_Load()
DoCmd.OpenReport "ExamTimeTablebydate", acViewPreview
End Sub

Private Sub cmdApplyFilter_Click()
Dim strOffice As String
Dim strDepartment As String
Dim strFilter As String


' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "ExamTimeTablebydate") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Office field
If IsNull(Me.cboOffice.Value) Then
strOffice = "Like '*'"
Else
strOffice = "='" & Me.cboOffice & "'"
End If
' Build criteria string for Department field
If IsNull(Me.cboDepartment.Value) Then
strDepartment = "Like '*'"
Else
strDepartment = "='" & Me.cboDepartment & "'"
End If
' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Class] " & strOffice & " AND [Venue] " & strDepartment
' Apply the filter and switch it on
With Reports![ExamTimeTablebydate]
.Filter = strFilter
.FilterOn = True
End With

[pbaldy]

Not sure I'd do it that wy, but this would be a tool to check the filter:

http://www.baldyweb.com/ImmediateWindow.htm

[June7]

I would build the filter string then open the report using WHERE CONDITION argument

Try:

If Not IsNull(Me.cboOffice) Then
         strFilter = "Office='" & Me.cboOffice & "'"
End If
If Not IsNull(Me.cboDepartment) Then
         strFilter = strFilter & IIf(strFilter <>"", " AND ") & "Venue='" & Me.cboDepartment & "'"
End If
DoCmd.OpenReport "ExamTimeTablebydate", acViewPreview, , strFilter

[George]

Thank you.

The code was copy and pasted but, it result in a popup dialog box requesting a value be entered for the 'office'. No request was required for the 'Venue'. So it seems like the string is not passed to the query.

If no value is entered in the parameter box, no results are returned.

[George]

If Not IsNull(Me.cboDepartment) Then
strFilter = strFilter & IIf(strFilter <>"", " AND ") & "Venue='" & Me.cboDepartment & "'"
End If
DoCmd.OpenReport "ExamTimeTablebydate", acViewPreview, , strFilter
[/code][/QUOTE]

[George]

Thanks. My problem is solved. Your method was so simple to compare with what I started with.

[June7]

Sorry, your field name is Class, not Office. My bad.

If you want results if nothing entered, then use the LIKE operator structure and no need for the conditionals.

DoCmd.OpenReport "ExamTimeTablebydate", acViewPreview, , "Class LIKE '" & Me.cboOffice & "*' AND Venue LIKE '" & Me.cboDepartment & "*'"

[George]

Thanks. I figured it out. I will try the other statements.