[SOLVED] Multiple filter between date and category on a subform in MS access >> Type mismatch error

Multiple filter between date and category on a subform in MS access >> Type mismatch error

I'm new for the MS Access development. I have been getting a problem to filter subform between filter date and category. It show an error statement that "Type mismatch". I try a lot, but cannot solve this issue.

Private Sub btn_SearchFilter_Click()

Dim FilterDate, FilterCategory As String

Me.Refresh
If IsNull(Me.filter_DateStart) Or IsNull(Me.filter_DateEnd) Or IsNull(Me.filter_ContourCategory) Then
    MsgBox "Please, enter value", vbCritical, "Error notification"
    Me.filter_DateStart.SetFocus

    Else
        FilterDate = "[Cont_RegisterDate] Between #" & Me!filter_DateStart.Value & "# And #" & Me!filter_DateEnd.Value & "#"
        FilterCategory = "Cont_Category='" & Me.filter_ContourCategory.Value & "'"
        Me!ContourRegisterSub.Form.Filter = FilterDate And FilterCategory
        Me!ContourRegisterSub.Form.FilterOn = True
        
End If

End Sub

Result:

Error "Syntax mismatch"

However, it can filter, if I try to filter only one string (FilterDate or FilterCategory) as shown below.

Private Sub btn_SearchFilter_Click()

Dim FilterDate As String

Me.Refresh
If IsNull(Me.filter_DateStart) Or IsNull(Me.filter_DateEnd) Then
    MsgBox "Please, enter value", vbCritical, "Error notification"
    Me.filter_DateStart.SetFocus

    Else
        FilterDate = "[Cont_RegisterDate] Between #" & Me!filter_DateStart.Value & "# And #" & Me!filter_DateEnd.Value & "#"
        Me!ContourRegisterSub.Form.Filter = FilterDate
        Me!ContourRegisterSub.Form.FilterOn = True
        
End If

End Sub

Result: Able to filter date

Private Sub btn_SearchFilter_Click()

Dim FilterCategory As String

Me.Refresh
If IsNull(Me.filter_ContourCategory) Then
    MsgBox "Please, enter value", vbCritical, "Error notification"
    Me.filter_ContourCategory.SetFocus

    Else
        FilterCategory = "Cont_Category='" & Me.filter_ContourCategory.Value & "'"
        Me!ContourRegisterSub.Form.Filter = FilterCategory
        Me!ContourRegisterSub.Form.FilterOn = True
        
End If

End Sub

Result: Able to filter category

How to solve this problem?

Solution

You must concatenate the filter conditions:

Else
    FilterDate = "[Cont_RegisterDate] Between #" & Format(Me!filter_DateStart.Value, "yyyy\/mm\/dd") & "# And #" & Format(Me!filter_DateEnd.Value, "yyyy\/mm\/dd") & "#"
    FilterCategory = "Cont_Category='" & Me!filter_ContourCategory.Value & "'"
    Me!ContourRegisterSub.Form.Filter = FilterDate & " And " & FilterCategory
    Me!ContourRegisterSub.Form.FilterOn = True