Filter by date range for any one of three dates.

[manderson_zim]

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
Else
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.

I'd appreciate any help!

 

[DCrake]

You will need to include three seperate Between And statements as OR conditions.

(AND Conditions) OR (Date Range 1)
(AND Conditions) OR (Date Range 2)
(AND Conditions) OR (Date Range 3)

 

[manderson_zim]

David,

Thanks for the speedy reply. The problem for me is that I am such a beginner with this that I need to see an example of what that means. Sorry! But if you don't mind giving me the time, then what would the code look like that I need to put in?

Thanks for your patience!
Mark

 

[DCrake]

Take a look at the layout below

 

[manderson_zim]

Ok, I see. But this is in my query. I need it to be in my Search form as the date range will be entered by the user and change each time

 

[DCrake]

Yes but if you create a matching query then look at the SQL view you will see how you need to construct the sql in vba