Including blank fields in results when filtering a report

[manderson.zim]

I am very new to Access 2007, and am trying to write code for a form named Search that filters a report named BibleStudiesDatabase.

There are six fields on the Search form that may be used to filter the report when the cmdFilter button is pressed: Book, Chapter, Themes, Location, Title, and Series. When one of these is left blank in the filter, I want the report to display all records for that field. However, some of the fields are blank and these are not being displayed in the results. I know it has something to do with the Like expression ignoring Null fields, but I have no idea how to fix it! Any help is appreciated.

Here is part of the code that I am using on the Search form:

Private Sub cmdFilter_Click()

Dim strBook As String
Dim strChapter As String
Dim strThemes As String
Dim strLocation As String
Dim strTitle As String
Dim strSeries 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
' Build criteria string for searching by Themes
If IsNull(Me.txtThemes.Value) Then
strThemes = "Like '*'"
Else
strThemes = "Like '*" & Me.txtThemes.Value & "*'"
End If
' Build criteria string for searching by Location
If IsNull(Me.txtWhere.Value) Then
strLocation = "Like '*'"
Else
strLocation = "Like '*" & Me.txtWhere.Value & "*'"
End If
' Build criteria string for searching by Title
If IsNull(Me.txtTitle.Value) Then
strTitle = "Like '*'"
Else
strTitle = "Like '*" & Me.txtTitle.Value & "*'"
End If
' Build criteria string for searching by Series Name
If IsNull(Me.txtSeries.Value) Then
strSeries = "Like '*'"
Else
strSeries = "Like '*" & Me.txtSeries.Value & "*'"
End If
' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Book]" & strBook & "AND [AllPlaces]" & strLocation _
& "AND [Themes]" & strThemes _
& "AND [Chapter]" & strChapter _
& "AND [Title]" & strTitle _
& "AND [Series]" & strSeries _

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

End Sub

 

[manderson.zim]

No worries - I got the answer online thanks to Doug Steele:

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