Filtering by text or date range

[Ashe]

Hello,



I have a form with several combo boxes and two text boxes to enter a date range. The form is used to filter results and print them in a report.


I am using a WhereCondition:= Mid(strWhere, 6) to remove the “ AND ” in the string. It works fine for the results when filtered by the combo box, but I get a syntax error when trying to filter by the date range.



The run-time error says “Syntax error (missing operator) in query expression ‘tiated Date] >= #03/04/1900#) AND ([Initiated Date] < #/03/03/2011#)’. I guess at times the WhereCondition is cutting off the first date range column title ([Initiated Date] chopped down to “tiated Date]”)?



I’ve put a cliff note version of my code below, which was adapted from an Allen Brown example (http://allenbrowne.com/casu-08.html). Any help would be appreciated! Thanks!

'This code applies the filters as chosen by the user for the detailed report
Private Sub cmdCreateDetailedReport_Click()
    Dim strWhere As String
    Dim strDateField As String
    Const strcJetDate = "\#mm\/dd\/yyyy\#"
    
    If Len(Me.cboProject & "") > 0 Then
    strWhere = strWhere & " AND [Project Name]= '" & Me.cboProject & "'"
    End If
    If Len(Me.cboSubject & "") > 0 Then
    strWhere = strWhere & " AND [Subject]= '" & Me.cboSubject & "'"
    End If
    
    'For search-by-date-option
    'If the cboDate box is filled out
    If Len(Me.cboDates & "") > 0 Then
    'then look at what is in the cboDate box
        If Me.cboDates = "Start Date" Then
        strDateField = "[Initiated Date]"
        'go to the commitment start date and look for the dates
            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
        
    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
    
    If Len(strWhere & "") = 0 Then
    DoCmd.OpenReport "rpt_CommitmentsDetailed", acViewPreview
    Else
    DoCmd.OpenReport "rpt_CommitmentsDetailed", acViewPreview, Mid(strWhere, 6)
    End If
    'DoCmd.Close
End Sub

[pbaldy]

This will drop any previous contents, and doesn't include the leading " AND " that your following code assumes is there:

strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"

[Ashe]

I think that's what I have on Line 22 of the code I posted. Is there a subtlety that I'm missing?

[pbaldy]

Yes, that's what you have now. What you have elsewhere is:

strWhere = strWhere & " AND ...

So as I said, your existing code on line 22 will drop any previous value in strWhere, and if there is none it's not including the " AND " that others do, which is why when you start at position 6 you lose part of your criteria.

[Ashe]

Finally got it. Thank you!

[pbaldy]

Happy to help!