Access VBA中LIKE实现动态搜索框短字符过滤异常排查

问题表现 #

• 子窗体动态搜索框搭配预设筛选按钮的功能在搜索词长度≥3时运行正常，搜索词短于3时匹配结果异常：输入Hä时，会将Natronlauge 50% techn. EN 896这类完全不包含搜索词的记录纳入结果，输入第三个字符后匹配恢复正常。
• 异常逻辑出现在过滤条件拼接行：

Me.Form.Filter = "[bezeichnung] LIKE '*" & SearchBoxStoffe & "*' AND [kategorie] = '" & Forms![HUB]![FilterAlleLink] & "'"

附原始完整实现代码：

Private Sub SearchBoxStoffe_KeyUp(KeyCode As Integer, Shift As Integer)

On Error GoTo errHandler

Dim filterText As String

'Apply dynamic filter for current filter category.
If Len(SearchBoxStoffe.Text) > 0 Then
    filterText = SearchBoxStoffe.Text
    If Forms![HUB]![FilterAlleLink] = "" Then
        Me.Form.Filter = "[bezeichnung] LIKE '*" & SearchBoxStoffe & "*'"
    Else
        Me.Form.Filter = "[bezeichnung] LIKE '*" & SearchBoxStoffe & "*' AND [kategorie] = '" & Forms![HUB]![FilterAlleLink] & "'"
    End If
    Me.FilterOn = True
    'Retain filter text in search box after refreshing.
    SearchBoxStoffe.Text = filterText
    SearchBoxStoffe.SelStart = Len(SearchBoxStoffe.Text)
Else
    'Revert to current main filter category.
    If Forms![HUB]![FilterAlleLink] <> "" Then
        Call FilterStoffe("[kategorie] = '" & Forms![HUB]![FilterAlleLink] & "'")
    Else
        If Forms![HUB]![FilterAlleLink] = "" Then
            Me.Filter = ""
            Me.FilterOn = False
        End If
    End If
End If

'Set focus back to search box
SearchBoxStoffe.SetFocus

Exit Sub

errHandler:

MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."

End Sub

故障原因 #

1. 事件触发时取值错误：KeyUp事件中直接引用SearchBoxStoffe控件取到的是绑定的Value属性（未实时同步输入内容），而非代表当前输入框实时内容的Text属性，短文本输入时Value未同步更新，导致拼接的过滤条件和实际输入不一致。
2. Access默认LIKE运算符匹配逻辑缺陷：默认LIKE走Jet/ACE引擎的本地排序规则匹配，对带变音符号的非ASCII字符（如德语ä/ö/ü）支持近似匹配，且搜索词短于3个字符时会走索引快速匹配逻辑，不会做全字段逐字符扫描，导致误匹配。
3. 未做特殊字符转义：输入内容如果包含单引号、LIKE通配符（*/?/#/[]），会直接打乱SQL语法或者匹配逻辑。

修复方案 #

1. 所有实时输入取值统一用SearchBoxStoffe.Text，确保拼接的搜索词和用户输入完全一致。
2. 用ALIKE关键字替换LIKE，搭配ANSI标准通配符%，强制走精确逐字符匹配，绕过本地排序规则的近似匹配和短词索引bug，单字符输入也能返回准确结果。
3. 对输入内容做转义处理，避免单引号触发语法错误、通配符打乱匹配逻辑。

修正后的核心代码 #

替换原有过滤条件拼接部分即可：

If Len(SearchBoxStoffe.Text) > 0 Then
    Dim safeSearchText As String, safeCategory As String
    ' 转义搜索文本
    safeSearchText = Replace(SearchBoxStoffe.Text, "'", "''")
    safeSearchText = Replace(safeSearchText, "[", "[[]")
    safeSearchText = Replace(safeSearchText, "*", "[*]")
    safeSearchText = Replace(safeSearchText, "?", "[?]")
    safeSearchText = Replace(safeSearchText, "#", "[#]")
    
    If Forms![HUB]![FilterAlleLink] = "" Then
        Me.Form.Filter = "[bezeichnung] ALIKE '%" & safeSearchText & "%'"
    Else
        safeCategory = Replace(Forms![HUB]![FilterAlleLink], "'", "''")
        Me.Form.Filter = "[bezeichnung] ALIKE '%" & safeSearchText & "%' AND [kategorie] = '" & safeCategory & "'"
    End If
    Me.FilterOn = True
    ' 保留搜索框输入内容与光标位置
    SearchBoxStoffe.SelStart = Len(SearchBoxStoffe.Text)
Else
    ' 回退到分类筛选逻辑
    If Forms![HUB]![FilterAlleLink] <> "" Then
        Call FilterStoffe("[kategorie] = '" & Replace(Forms![HUB]![FilterAlleLink], "'", "''") & "'")
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
End If

注：原有代码中重复判断Forms![HUB]![FilterAlleLink] = ""的冗余逻辑已经删除，不影响原有功能。

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