Filter multiple fields in a report with a form and allow blank fields to be returned (1 Viewer)

[manderson_zim]

I am very new to access 2007 and struggling with the code for a form named frmSearch that I am using to filter a report named rptBibleStudies.

There are two frames on the form which each filter one of the following fields: [Book] and [Chapter]. If the field is left blank in the frmSearch, then I want the report to display all records for that field. It is working well except for one thing; some fields are left blank during data entry and these records are not being returned after filtering. For example: if during data entry the [Chapter] field is left blank and the user does not enter a value to filter [Chapter] by in the frmSearch, then this record will not be included in the rptBibleStudies.

I know it has something to do with the expression Like and Null fields, but I don't know how to fix it. Here is part of the code used:

Private Sub cmdFilter_Click()

Dim strBook As String
Dim strChapter As String
Dim strFilter As String

' Build criteria string for searching by Book
If IsNull(Me.cbobook.Value) Then
strBook = "Like '*'"
Else
strBook = "='" & Me.cbobook.Value & "'"
End If

' Build criteria string for searching by Chapter
If IsNull(Me.txtChapter.Value) Then
strChpater = "Like '*'"
Else
strChapter = "Like '*" & Me.txtChapter.Value & "*'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Book]" & strBook & "AND [Chapter]" & strChapter _

' Apply the filter and switch it on
With Reports![rptBibleStudies]
.Filter = strFilter
.FilterOn = True
End With

[FONT=&quot]End Sub[/FONT]

 

[ted.martin]

Have you tried using LEN? Maybe try this:

if len(Me.cbobook.Value & "") = 0 then

etc

This traps Null and Empty String fields in one pass of code.

 

[manderson_zim]

Not quite I'm afraid... with that code change I still have the same problem in that if I leave a field blank in the Search form the report gives me all records for that field except any that are blank themselves!

I'm pretty sure that the part of the code that needs to change is the Like expression in the Then part, rather than altering the If part.

 

[JANR]

' Apply the filter and switch it on
With Reports![rptBibleStudies]
.Filter = strFilter
.FilterOn = True
End With

I don't think you can apply a filter to a report that is not open.

Why not you DoCmd.OpenReport and pass strFilter to the where parameter of this command.

DoCmd.OpenReport "rptBibleStudies",,, strFilter

JR

 

[vbaInet]

Actually the report's Filter is fine. It's the only dynamic way of filtering a report on-the-fly.

@manderson_zim:

You need to re-think your form design. Give the users the option of selecting blank records, so if you added the word <Blank> to the Row Source of your combo box the user knows that that selection is for blank records within that field. If you follow this route, your code will look like this:

Private Sub cmdFilter_Click()

    Dim strBook As String
    Dim strChapter As String
    Dim strFilter As String
   
    ' Build criteria string for searching by Book
    If Not IsNull(Me.cbobook.Value) Then
        If Me.cbobook.Value = "<Blank>" Then
            strBook = "[Book] Is Null"
        Else
            strBook = "[Book] Like '*" & Me.cbobook.Value & "'"
        End If
    End If
   
    ' Build criteria string for searching by Chapter
    If Not IsNull(Me.txtChapter.Value) Then
        If Me.txtChapter.Value = "<Blank>" Then
            strChapter = "[Chapter] Is Null"
        Else
            strChapter = "[Chapter] Like '*" & Me.txtChapter.Value & "*'"
        End If
       
        ' Combine criteria strings into a WHERE clause for the filter
        If Len(strBook) <> 0 Then
            strFilter = strBook & " AND " & strChapter
        End If
    End If

    ' Apply the filter and switch it on
    If Len(strFilter) <> 0 Then
        With Reports![rptBibleStudies]
            .Filter = strFilter
            .FilterOn = True
        End With
    End If

End Sub

UG Changed:-

If Me.txtChapter.Value = "[COLOR=Red]<Blank>[/COLOR]" Then
'To:-
If Me.txtChapter.Value = "<Blank>" Then

'And

If Me.cbobook.Value = "[COLOR=Red]<Blank>[/COLOR]" Then
'To:-
If Me.cbobook.Value = "<Blank>" Then

 

[manderson_zim]

Thanks all, good points. But I've gone with the following that seems to do what I need:

Private Sub cmdFilter_Click()
 
Dim strFilter As String
 
' Build criteria string for searching by Book
  If IsNull(Me.cbobook.Value) = False Then
    strFilter = "([Book]='" & Me.cbobook.Value & "' Or [Book] IS NULL) AND "
  End If
 
' Build criteria string for searching by Chapter
  If IsNull(Me.txtChapter.Value) = False Then
    strFilter = strFilter & "([Chapter] Like '*" & Me.txtChapter.Value & "*' Or [Chapter] IS NULL) AND "
  End If
 
  If Len(strFilter) > 0 Then
    strFilter = Left$(strFilter, Len(strFilter) - 5)
' Apply the filter and switch it on
    With Reports![BibleStudiesDatabase]
      .Filter = strFilter
      .FilterOn = True
    End With
  Else
' Turn off the filter
    With Reports![BibleStudiesDatabase]
      .Filter = vbNullString
      .FilterOn = False
    End With
  End If
   
End Sub