Date Range Filter (1 Viewer)

[dkinnz]

Hey everyone,

I'm trying to implement an example of a date range filter that I found (it uses a start date text box and end date text box), but I'm having a lot of trouble getting the code right. I'm trying to combine the date filter criteria with another combo box filter criteria. Here's what I have:

Private Sub cmdSearch_Click()
    Me.[subfrmDate].Form.RecordSource = "SELECT * FROM qryDate " & FilterIt
    Me.[subfrmDate].Requery
End Sub

Private Function FilterIt() As Variant

Dim varFam As Variant
Dim strField As String

Const conDateFormat = "\#mm\/dd\/yyyy\#"
strField = "Date"
strWhere = "1=1 "

If Me.cboTest <> "" Then
    strWhere = strWhere & "and [TestField]='" & Me.cboTest & "' "
End If

If IsNull(Me.txtStart) Then
        If Not IsNull(Me.txtEnd) Then   'End date, but no start.
            strWhere = strField & " <= " & Format(Me.txtEnd, conDateFormat)
        End If
Else
        If IsNull(Me.txtEnd) Then       'Start date, but no End.
            strWhere = strField & " >= " & Format(Me.txtStart, conDateFormat)
        Else                                'Both start and end dates.
            strWhere = strField & " Between " & Format(Me.txtStart, conDateFormat) _
                & " And " & Format(Me.txtEnd, conDateFormat)
        End If
End If

    FilterIt = "Where " & strWhere
    
End Function

I can't figure out how to put the "AND" so that if either the start date, end date, or both start and end dates are given, the filter will combine with the selection from cboTest to filter the results. Note: the user may not always enter a date.

Thank you very much for any help!!
dkinnz

 

[dkinnz]

got it working...here's what i did

Dim varFam As Variant
Dim strField As String
Dim strWhere As Variant
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strField = "Date"
strWhere = "1=1 "


If Me.cboTest <> "" Then
    varFam = varFam & "and [TestField]='" & Me.cboTest & "' "
End If

If IsNull(Me.txtStart) Then
        If Not IsNull(Me.txtEnd) Then   'End date, but no start.
            strWhere = strField & " <= " & Format(Me.txtEnd, conDateFormat)
        End If
Else
        If IsNull(Me.txtEnd) Then       'Start date, but no End.
            strWhere = strField & " >= " & Format(Me.txtStart, conDateFormat)
        Else                                'Both start and end dates.
            strWhere = strField & " Between " & Format(Me.txtStart, conDateFormat) _
                & " And " & Format(Me.txtEnd, conDateFormat)
        End If
End If

strWhere = strWhere & varFam
    FilterIt = "Where " & strWhere
    
End Function