Filter form records by date range using two unbound text boxes

Viewed 78

Good afternoon. I have a continuous form which I want to allow the user to filter by using unbound boxes in the form header. See screenshot of form below: Form Design View

I can filter error type (Unbound: cboErrorType) and responsibility (Unbound: cboResponsibility) without any issues, but I cannot get the date range to work (Records outside of the selected date range are being returned). Please see screenshot:

Form - filtered

Please see my code below:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#dd\/mm\/yyyy\#"


If Not IsNull(Me.cboErrorType) Then
    strWhere = strWhere & "([ErrorType] = """ & Me.cboErrorType & """) AND "
End If

If Not IsNull(Me.cboResponsibility) Then
    strWhere = strWhere & "([Decision] = """ & Me.cboResponsibility & """) AND "
End If

If Not IsNull(Me.txtStartDate) Then
    strWhere = strWhere & "([Date of Error] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then
    strWhere = strWhere & "([Date of Error] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
    MsgBox "No criteria", vbInformation, "Nothing to do."
Else
    strWhere = Left$(strWhere, lngLen)
    'Debug.Print strWhere
    
    Me.Filter = strWhere
    Me.FilterOn = True
End If
End Sub

I am removing the filters with the following code:

Private Sub cmdResetFilter_Click()
Dim ctl As Control
For Each ctl In Me.Section(acHeader).Controls
    Select Case ctl.ControlType
    Case acTextBox, acComboBox
        ctl.value = Null
    End Select
Next
Me.FilterOn = False

End Sub

Please can someone suggest a solution? If further information is required, let me know and I will oblige.

Many thanks.

1 Answers

The date format must be either the "reverse" US format (mm/dd/yyyy) or the ISO format (which also works for ADO). So try:

Const conJetDate = "\#yyyy\/mm\/dd\#"
Related