Filter by date range for any one of three dates.

Mar 30, 2011
Reaction score
I am working with a form called frmSearch that filters a report called rptStudies. I am already filtering two fields [Book] and [Chapter] successfully with the following code:


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

' Run the Filter
If Len(strFilter) > 0 Then
strFilter = Left$(strFilter, Len(strFilter) - 5)
With Reports![rptStudies]
.Filter = strFilter
.FilterOn = True
End With
With Reports![rptStudies]
.Filter = vbNullString
.FilterOn = False
End With
End If
End Sub

I now want to include in that filter a date range. The report should only return dates between values entered into txtStartDate and txtEndDate on the frmSearch.

Here is my problem: How do I make sure that my report returns all dates between that range when there are three dates on the report [Date1] [Date2] and [Date3]. I need the record to be returned if ANY of the three dates is within the range.

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