Filter Help Needed

[accessma]

I am trying to filter a report by a date range using the code below. I know the filter string is supposed to be something like:

([StartDate] >= #strfilter#) AND ([EndDate] <= #strfilter#)

I just cant get it. I have a text box for the StartDate and one for the EndDate.

Can someone show me what my code should look like? Thanks in advance.

Private Sub cmdFilterByDate_Click()
Dim strDoc As String
strDoc = "SummaryReport"
'Set Date Range Filter
    If Nz(Me.txtStartDate, vbNullString) <> vbNullString And Nz(Me.txtEndDate, vbNullString) <> vbNullString Then
        If Me.txtStartDate > Me.txtEndDate Then
            MsgBox "Start Date Cannot Be Greater Than End Date", vbInformation
            Exit Sub
        End If
    End If
    If Nz(Me.txtStartDate, vbNullString) <> vbNullString Then
        strFilter = strFilter & " AND [StartDate] >= #" & Me.txtStartDate & "#"
        
    End If
    
    If Nz(Me.txtEndDate, vbNullString) <> vbNullString Then
        strFilter = strFilter & " AND [ProjectedEndDate] <= #" & Me.txtEndDate & "#"
        
    End If
    
    DoCmd.OpenReport strDoc, acViewPreview, , strFilter

 

[DCrake]

Simple Software Solutions

Hi

Your filter string suggests that it begns with ANDIs this correct?

You need to examine the full context of the StrFilter bfore you pass it to the DoCmd.

Alternatively have a look at this previous post regarding dates and filters.

CodeMaster:http://www.icraftlimited.co.uk

 

[accessma]

Ok, Here is some more information I neglected to post. The code I am trying to use I use in another routine which is pasted below which works fine:

 Private Sub cmdApplyFilter_Click()
    Dim strFilter As String
    Dim blnFilterOn As Boolean
    Dim strCriteria As String
    
    strCriteria = "Criteria: "
    
          
    'Set Date Range Filter
    If Nz(Me.txtStartDate, vbNullString) <> vbNullString And Nz(Me.txtEndDate, vbNullString) <> vbNullString Then
        If Me.txtStartDate > Me.txtEndDate Then
            MsgBox "Start Date Cannot Be Greater Than End Date", vbInformation
            Exit Sub
        End If
    End If
    If Nz(Me.txtStartDate, vbNullString) <> vbNullString Then
        strFilter = strFilter & " AND [MyDate] >= #" & Me.txtStartDate & "#"
        strCriteria = strCriteria & vbCrLf & "Begin Date: " & Me.txtStartDate
    End If
    
    If Nz(Me.txtEndDate, vbNullString) <> vbNullString Then
        strFilter = strFilter & " AND [MyDate] <= #" & Me.txtEndDate & "#"
        strCriteria = strCriteria & vbCrLf & "End Date: " & Me.txtEndDate
    End If
    
    If strFilter <> vbNullString Then
        strFilter = Mid(strFilter, 6)
        Me.txtCriteria = strCriteria
        Reports("SummaryReport").Filter = strFilter
        Reports("SummaryReport").FilterOn = True
    Else
        MsgBox "Enter some criteria", vbInformation
    End If
    
    
End Sub

So I am trying to adapt the dater part to the new form I built using listboxes instead of a combo box. I guess I am asking what the difference is in the code working in one routine and not another. Far from an expert here.

 

[DCrake]

Simple Software Solutions

Your code now passess the .Fitler to the report but you have not opened the report.


Also you are doubling up on your strFilter string

After the line

strFilter = Mid(strFilter, 6)

Add the following:
Msgbox strfilter OR Debug.Print StrFilter

and you will see what I mean

 

[accessma]

OK Just to be clear, the first bit of code I posted at the top is what does not work in the current form I am trying to build. The second bit of code I posted is from a totally different form which works fine. So what do I need to use from code set #2 in the first bit of code I posted in order to make the filter work?

As for the repeating code in the second code I posted I dont need two checks, one for start date and one for end date?

 

[DCrake]

Simple Software Solutions

Is this the same report that is being opened from two different forms?

If so, have you looked at the attachment detailing the use of public variables accross the application lifespan?

David

 

[accessma]

This report is opened by two other buttons on the form. One for a department selection and another time for an employee selection. Now I want to open it again for a date range filter. Here is my latest code and the FE and BE of the DB.

Private Sub cmdFilterByDate_Click()
Dim strDoc As String
strDoc = "SummaryReport"
'Set Date Range Filter
    If Nz(Me.txtStartDate, vbNullString) <> vbNullString Then
        strFilter = strFilter & " AND [StartDate] >= #" & Me.txtStartDate & "#"
    End If
    
    If Nz(Me.txtEndDate, vbNullString) <> vbNullString Then
        strFilter = strFilter & " AND [ProjectedEndDate] <= #" & Me.txtEndDate & "#"
        
    End If
    
    If strFilter <> vbNullString Then
        strFilter = Mid(strFilter, 6)
        
        Reports("SummaryReport").Filter = strFilter
        Reports("SummaryReport").FilterOn = True
    Else
        MsgBox "Enter some criteria", vbInformation
    End If
    DoCmd.OpenReport "SummaryReport", acPreview

    
    'DoCmd.OpenReport strDoc, acViewPreview, , strFilter


End Sub