Filtering Report based on date range (and prior dates)

[justinwright]

I found the thread about one specific date (and pbaldy's site) which was a huge help, but I was wondering two things.



How do i filter a report based on a date range (controls on form)? txtDate1 and txtDate2


The code I have opening the report with just a specific date is as follows:

DoCmd OpenReport "RefDueSpecifiedDate", acViewPreview, , "PENDDATE = #" & Me.txtDate1 & "#"

And I want to incorporate for the range txtDate1 as the beginning date and txtDate2 as the ending date for the filter.

The next question is, how would I structure a filter that will return (based on txtDate1) the current date and any dates prior to that AND have a null value for PROCESSED (or NO value for PROCESSED) field in the table?

[justinwright]

Sorry about the double post, but I feel happy to be able to post the answer for others that I worked out

At the top of the private sub I put this:

Dim strReport as String
Dim strDateField as String
Dim strWhere As String
Const strcDateConst = "\#mm\/dd\/yyy\#"

To define the variables for use

Then put the following code in the proper if statement:

strReport = "EntrySpecificDateRange" 'report name
strDateField = "[DATEENTRY]" 'field name here

'Make filter string
If Isdate(Me.txtDate1) Then
    strWhere = "(" & strDateField & " >= " & Format(Me.txtDate1, strcDateConst) & ")"
End If

If IsDate(Me.txtDate2) Then
    If strWhere <> vbNullString Then
        strWhere = strWhere & " AND "
    End If
    strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtDate2 + 1, strcDateConst) & ")"
End If

 'Open Report
DoCmd.OpenReport strReport, acViewPreview, , strWhere
' acViewNormal for printing, acViewPreview for previewing

It seems like that makes the date range work fine, but how do I add something to check for the PROCESSED value as Null and NO?

How do I take txtDate1 and return all that dates and the date before where PROCESSED is Null or has a NO value?

[RuralGuy]

The Query builder know how to do all of this. Have you tried using it yet? If the Criteria is on the same row then it is an AND, and if you put it on a different Criteria Row then it is an OR. Post back here if you need further assistance.

[justinwright]

Well the only problem is that I don't have the first clue about queries.

I created one withboth :
Filter: PENDDATE PROCESSED
Table: TrackingTable TrackingTable
Sort:
Show: (Checked) (Checked)
Criteria =[Form]![RepFrm]![txtTodaysDate] ="No"
or: <[Form]![RepFrm]![txtTodaysDate]


but in reality I have no remote clue whatsoever what I'm doing when it comes to queries. That's probably so far off the mark it isn't even funny, which is why I wanted to see if there was a way I could do it in VBA.

Thanks for the help though.

[RuralGuy]

Start by switching to SQL view and post what is there.

[justinwright]

SELECT TrackingTable.PENDATE, TrackingTable.PROCESSED
FROM TrackingTable
WHERE(((TrackingTable.PENDDATE)=[Form]![RepFrm]![txtTodaysDate])AND ((TrackingTable.PROCESSED)="No")) OR (((TrackingTable.PENDDATE)<[Form]![RepFrm]![txtTodaysDate]));

[ajetrumpet]

Justin,

for further help on dates, you may want to check out my dates page: http://www.ajenterprisesonline.com/ab/_dates/

I didn't read through this entire thread, but some of the things that people ask about dates can be solved by using that page. Just for reference...

[RuralGuy]

So you just want those two fields in your recordset?

[justinwright]

Yeah, I'm just looking to filter a report based on dates equal to and before txtTodaysDate (PENDDATE) that also have a No value for the PROCESSED field

[RuralGuy]

But your report wants all of the fields in the table right? You are only handing the report those two fields.

[justinwright]

Oh, so I put no criteria for the other fields that I want on the report (but still include them in the query)?

[RuralGuy]

Bingo! All of the fields for your report need to be puulled by your query and displayed. You do not need to display the fields with the selection criteria if you don't want to. You can have the same field listed twice with different criteria in each column and depending on whether the criteria is on the same row or a different row dictates AND or OR.

[justinwright]

Okay so I was totally lost with Queries, but thankfully I figured it out for VBA in case anyone was interested:

Dim strWhere As String
' Define variable for use

At top, then in the appropriate if statement:

If Me.cboRefunds.Value = "Today" Then
    ' Needs to incorporate the current day and any that are outstanding (past due date but not processed)
    
    strWhere = "[PROCESSED] = '" & "No" & "' AND PENDDATE <= #" & Me.txtTodaysDate & "#"
    
    DoCmd.OpenReport "RefDueCurrentDay", acViewPreview, , strWhere

Thank you for the help though, as always

[RuralGuy]

I like teaching people to fish rather than cooking a meal. Excellent!