Limiting Report to certain Dates

[Varda]

MS Access 2010


Intermediate level (not a beginner, but not an expert).
Assume little/none knowledge in SQL.
Some Visual Basic knowledge.


I have a table with several fields, a few of which are: ItemID, ItemNumber, ItemDescription, StatusID, ItemStartDate and ItemEndDate.

I am trying to print a report with some criteria.

I created a Form based on help from the internet (http://allenbrowne.com/casu-08.html) - "Method 2: Form for entering the dates." This form is great and works, but not exactly the way I would like it to.

This form has the field txtStartDate and txtEndDate.

I'm trying to print a report based on the following criteria:
1. ItemStartDate > txtStartDate AND ItemStartDate < txtEndDate
OR
2. ItemStartDate < txtStartDate AND (ItemEndDate = is null OR (ItemEndDate > txtStartDate AND ItemEndDate < txtEndDate))


In other words: If an item is still active and not closed during the period between txtStartDate and txtEndDate, I would like to see it on the report.

How do I adjust the code given to me in the link above to do what I'd like to achieve. I'm assuming I need to adjust the part of the code I highlighted below in blue font, but am not sure how to change it as I'm not very skilled at writing codes. (I'm a project manager by profession).

==============
Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.
'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"
strDateField = "[ItemStartDate]"
lngView = acViewPreview 'Use acViewNormal to print instead of preview.

'Build the filter string.
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

'Close the report if already open: otherwise it won't filter properly.
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

'Open the report.
'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
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
=======================

[orange]

see if this link helps with some conditions.

Also, I have reformatted your code for readability;

Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.
'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"
    strDateField = "[ItemStartDate]"
    lngView = acViewPreview    'Use acViewNormal to print instead of preview.

    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If

    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If

    'Open the report.
    'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
    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

[Varda]

I looked at the link you posted and it appears to be what I'm looking for, but I'm not sure how to insert this into my code.

I don't know SQL well enough to be able to translate it to the code above.

In my case I imagine the code would be something like:
SELECT ItemStartDate, ItemEndDate
FROM tblAllItems
WHERE
ItemStartDate <= Forms!frmWhatDates.txtEndDate And ItemEndDate >=
Forms!frmWhatDates.txtStartDate


But where do I put this in the code?

Varda

[orange]

Here's another link with some samples and tutorials.
From what I understand of your post, a form from which to select and launch your report seems appropriate.
Martin Green has good examples of many things on his site.

[Varda]

Thank you for the link. Interesting examples there. Unfortunately, none of them are similar to my example.

I figured out the form from the original link I posted: (http://allenbrowne.com/casu-08.html) -

The problem is that the code behind the form is not exactly what I'm trying to achieve. At this time, I'm unable to print any items that are dated before the txtStartDate and are still not complete. Nor am I able to print anything that was closed during the period of txtStartDate and txtEndDate.

[Varda]

MS Access 2010
Intermediate level (not a beginner, but not an expert).
Assume little knowledge in SQL.
Some Visual Basic knowledge.


I have a table with several fields, a few of which are: ItemID, StatusID, ItemStartDate and ItemEndDate.

I am trying to print a report with criteria limiting to a date range. (The assumption is that if there is no ItemEndDate, the Status is still Active - and then I'd like to see this item on the report).

I created a Form frmWhatDates based on help from the internet (http://allenbrowne.com/casu-08.html) - "Method 2: Form for entering the dates." This form is great but it only looks at one data entry field, and I need to compare against two (txtStartDate and txtEndDate).


The scenario I'm looking for is shown nicely in the diagram in this link: http://www.baldyweb.com/OverLap.htm

I'm trying to print a report based on the following criteria:
1. When both txtStartDate and txtEndDate are entered: the filter should be --- ItemStartDate <= Forms!frmWhatDates.txtEndDate AND ItemEndDate >= Forms!frmWhatDates.txtStartDate
2. When only a txtStartDate is entered: the filter should be --- show all items without ItemEndDate. [since it means "Active items that have not been closed yet."]

In other words: If an item is still "active" and not "closed" during the period between txtStartDate and txtEndDate, I would like to see it on the report.

I am trying the following code, but it's still not working. It is printing out one item (amongst others) that has ItemEndDate earlier than txtStartDate. And I thought my code was going to eliminate this scenario.

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    '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.
    
    'DO set the values in the next 3 lines.
    strReport = "rptAllItems_Reports_SelectDate"      'Put your report name in these quotes.
    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.
    
    
    'Build the filter string.
     If IsDate(Me.txtStartDate) Then
         strWhere = "(" & strDateFieldStart & " <= " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
     End If
     If IsDate(Me.txtEndDate) Then
         If strWhere <> vbNullString Then
             strWhere = strWhere & " AND " & "(" & strDateFieldEnd & " >=" & Format(Me.txtStartDate, strcJetDate) & ")"
         End If
         strWhere = ""               'not sure if this is right. I want to print all items without strDateFieldEnd.
     End If

    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    'Open the report.
    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

[June7]

I merged your two threads on the same issue.

Modify the code as needed. If you need the If Then conditional to consider both fields then adjust code. Use as many If Then structures as needed. Maybe an If Then ElseIf.

If IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then
...
ElseIf IsDate(Me.txtStartDate) Then
...
End If

[Varda]

Several months have passed. The code I figured out worked great, but now I need it to reference date fields on a SUBREPORT.

Can someone please help me?

I believe I referenced the subreport correctly, but it continues to open the subreport for ALL records and ignoring the limitations of the dates. What am I doing wrong?

Here's the code I'm using:

Private Sub cmdrptAllItems_Summary_SelectDates_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 set the values in the next 3 lines.
    strReport = "rptAllItems_Summary_SelectDates"      
    strDateFieldStart = "Reports![rptAllItems_Summary_SelectDates]![rptAllItems_Summary_Done].Report![ItemStartDate]" 
    strDateFieldEnd = "Reports![rptAllItems_Summary_SelectDates]![rptAllItems_Summary_Done].Report![ItemEndDate]" 
    lngView = acViewPreview    
    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
    
    'Open the report.
    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

[June7]

By subreport you mean a report that is on another report? Not opened independently? Subreports cannot be 'opened', they just tag along with the main report. Code to direct opening of subreport should error. So I am confused and don't really understand what you are trying to do.

I always name subform/subreport container controls different from the objects they hold, like: ctrDetails. Then code to reference fields/controls of subform/subreport would be referenced through the container control name.

[Varda]

By subreport I mean a report that is within another report. So yes, not opened independetly. What I'm trying to do is:
1. I have a form where I enter txtStartDate and txtEndDate.
2. On the form is a button with code on it (see above).
3. When clicking the button I want rptAllItems_Summary_SelectDates to open in Preview mode.
4. I want the Subreport within it to show the records that fall within the range of dates selected on the Form (the range of what I want to show is irrelevant for this thread and is handled fine in the code posted).

Originally, I had the button on the form open the rptAllItems_Summary_SelectDates directly to all the records that fall within the range. But I then had to move the "detail" section to a Subreport embedded inside this report.

The subreport is a control within the report, and the name of it is rptAllItems_Summary_Done (the source is: Report.rptAllItems_Summary_Done).

I tried changing the name of the control to ctr_rptAllItems_Summary_Done - but I get the same results: when I click the button on the form, it opens the report, and the subreport reflects ALL records and not limited to the range I want.


I'm not sure what I'm doing wrong, because the records are coming up, there's no error message. But it's just that they're not showing the range of records I want. The code works fine when the records are in the Detail section of the Report, so I know the issue is with how I reference the fields in the subreport.

Would appreciate any guidance.

Varda

[Varda]

I always name subform/subreport container controls different from the objects they hold, like: ctrDetails. Then code to reference fields/controls of subform/subreport would be referenced through the container control name.

I read your sentence many times and I googled container controls, but I still can't figure out what the sentence means.

What is "container controls"?

I'm assuming the object in my case is a subreport?

So what should I name differently?

I don't understand the last sentence either.


Can you please explain it? Keep in mind, I do not have a computer programming degree. My degree is in Architecture and Construction Management.



Varda

[ItsMe]

What is "container controls"?

Forms are objects in Access. They are Named and saved as Objects. There are other objects too. For example, a Query can be an object within Access. These types of objects are visible within the Navigation Pane.

When designing a form and adding a subform Object to your Main Form, you need to add a control to the Main Form. This Subform Control "contains" an Object. The Object the subform control contains is the Form Object that is visible within the Navigation Pane. As mentioned before, the Form Object has a name. Similarly, the Control that contains the Form Object has a name.

To determine the name of your Subform Control that contains the Form Object, use your mouse pointer to swoop over the outer perimeter of the control. With the Subform control selected (not the form object contained within) look at the properties sheet. Under the Other tab is the name for the selected control.

[Varda]

Thank you for explaining the terminology. I understand it better now.

It doesn't effect my original question as I did refer to the subform control, yet the code is still not working right.

[ItsMe]

Code behind a form resides in its Module. When writing code within a form's module you can reference controls using the Me shortcut, e.g. Me.ControlName.

If you are writing code within a main form and want reference a control that resides within a subform, you can still use the Me shortcut. However, you will need to use the subform control's name and also indicate the type of object the subform control contains. So something like Me.subformcontrol.Form!controlName

The Me shortcut is used to avoid typing out the fully qualified name of an object. Objects typically reside within a Namespace. Including each namespace in its respective hierarchy will compose the fully qualified name.
I believe the following is correct...
Forms!MainForm!SubformControl.Form!ControlName

And here is a reference chart
http://access.mvps.org/access/forms/frm0031.htm

[June7]

So the main report is not filtered by date range?

Seems I've encountered this issue before - subreport needs supplemental filter independent of the main report. Think the solution was dynamic parameterized query as subreport RecordSource.

You say you want to apply date range filter to subreport, yet the code appears to apply filter to main report. And why would the date variables be set by reference to subreport which isn't even open? Date variables should be set by reference to controls on form.