MS Access 2010
Intermediate level (not a beginner, but not an expert)
Very little knowledge of SQL and Basic (assume - novice)
Gentle soul - needs hand holding; not looking to learn at this time, just looking for an answer ("feed me Seymour").

I have a database that tracks items (outstanding issues to be followed upon in a project management setting). Each item gets an ItemStartDate and ItemEndDate. (And a status designation, but not relevant to this question).

With the help of Google, Database Help Forums, Websites and a good amount of patience I managed to figure out how to create a non-bound form that asks the user to enter two dates: txtStartDate and txtEndDate. And then there's a command button on this form which is meant to open a Report (based on All Items) filtered in a way that would bring up any item that was still "open" during these two dates entered (the assumption is that if there's no ItemEndDate, the item is still open).
This website shows very nicely my desired range:
http://www.baldyweb.com/OverLap.htm

The code I got off the internet http://allenbrowne.com/casu-08.html and the adjustment I made to it, worked great for my report.

But then...I needed to place this report as a SUBREPORT and of course the command button on the filter form no longer works.

So how do I filter a SUBREPORT? What do I need to change in this following code to make the report open, and the subreport show filtered?

I'd appreciate any help I can get before I drown my sorrow in yet another non-caffeinated beverage of no nutritional value.

Varda

[code]
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Filter a report to a date range.
'Documentation: http://allenbrowne.com/casu-08.html
'Note: Filter uses "less than the next day" in case the field has a time component.
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change it to match your local settings.

strReport = "rptAllItems_Reports_SelectDates" 'I suspect this is where the issue is...The subreport is called rptAllItems_Weekly

strDateFieldStart = "[ItemStartDate]" 'Put your field name in the square brackets in these quotes.
strDateFieldEnd = "[ItemEndDate]" 'I added this field name because the original code only had one date field.
lngView = acViewPreview 'Use acViewNormal to print instead of preview.
strWhere = "(" & "(" & strDateFieldStart & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")" & " AND " & "(" & strDateFieldEnd & " >=" & Format(Me.txtStartDate, strcJetDate) & ")" & ")" & " OR " & "(" & strDateFieldEnd & "IS NULL" & ")"


If IsNull(Me.txtEndDate) Then
Me.txtEndDate = DateAdd("d", 7, Me.txtStartDate)
strWhere = "(" & "(" & strDateFieldStart & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")" & " AND " & "(" & strDateFieldEnd & " >= " & Format(Me.txtStartDate, strcJetDate) & ")" & ")" & " OR " & "(" & strDateFieldEnd & "IS NULL" & ")"
End If

If IsNull(Me.txtStartDate) Then
Me.txtStartDate = Date
strWhere = "(" & "(" & strDateFieldStart & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")" & " AND " & "(" & strDateFieldEnd & " >= " & Format(Me.txtStartDate, strcJetDate) & ")" & ")" & " OR " & "(" & strDateFieldEnd & "IS NULL" & ")"
End If


If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.close acReport, strReport
End If

Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler

End Sub
[\code]