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:
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.