MS Access子窗体按日期与类别筛选时出现类型不匹配错误求助

作为MS Access开发新手，尝试对子窗体同时按日期范围和类别进行筛选时，出现「类型不匹配」错误，但单独筛选日期或类别均可正常运行。

出错代码 #

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

执行结果 #

Error "Type mismatch"

单独筛选正常的代码示例 #

仅日期筛选 #

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

执行结果：可正常筛选日期

仅类别筛选 #

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

执行结果：可正常筛选类别

解决方案 #

错误根源有两个：

1. 字符串拼接错误：FilterDate And FilterCategory中，And是VBA的逻辑运算符，用于布尔值判断，不能用来拼接字符串。需要用字符串连接符&，并在两个条件之间加上SQL逻辑运算符AND，组成完整的筛选字符串。
2. 变量声明不严谨：Dim FilterDate, FilterCategory As String仅将FilterCategory声明为String类型，FilterDate默认是Variant类型，需修正为明确的String类型声明。

修正后的完整代码：

Private Sub btn_SearchFilter_Click()

' 明确声明两个变量为String类型
Dim FilterDate As String, 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 & "'"
        ' 用&拼接字符串，两个条件间用AND连接
        Me!ContourRegisterSub.Form.Filter = FilterDate & " AND " & FilterCategory
        Me!ContourRegisterSub.Form.FilterOn = True
        
End If

End Sub

说明 #

• 拼接后的筛选字符串会变成"[Cont_RegisterDate] Between #日期1# And #日期2# AND Cont_Category='类别值'"，符合Access筛选的语法要求。
• 修正变量声明可以避免后续因类型隐式转换导致的潜在问题。

内容的提问来源于stack exchange，提问作者Ployphet Veerasak