MS Access多选列表框检索分号分隔文本字段多选无结果如何解决

问题根因 #

你当前的逻辑存在核心问题：Access 静态查询不会解析MySectors文本框中存储的OR关键字，只会将整个拼接后的字符串作为单一匹配项，和Sector字段做全串模糊匹配。多选时拼接的字符串里包含OR Entity_Activity.Sector Like *这类SQL语法内容，业务数据的Sector字段不可能包含这些字符，自然返回空结果。

修复方案 #

推荐优先使用动态拼接SQL的方案，逻辑清晰、性能更好：

方案1：动态生成SQL（推荐） #

无需继续使用MySectors文本框，也无需原有列表框点击事件的拼接逻辑，直接在搜索按钮的点击事件中组装完整SQL，赋值给结果展示区域的记录源即可：

Private Sub btnSearch_Click() ' 替换为你实际的搜索按钮点击事件名称
    Dim strSQL As String
    Dim oItem As Variant
    Dim strSectorCond As String
    
    ' 基础SQL语句
    strSQL = "SELECT Entity_Activity.[Activity ID], Entity_Activity.[Project ID], Entity_Activity.Sector, Entity_Activity.[Activity Status], [List_Activity Status].[Activity Status], Entity_Project.[Project ID] " & _
    "FROM [List_Activity Status] INNER JOIN (Entity_Project INNER JOIN Entity_Activity ON Entity_Project.[Project ID] = Entity_Activity.[Project ID]) ON [List_Activity Status].[Activity Status ID] = Entity_Activity.[Activity Status] " & _
    "WHERE 1=1 "
    
    ' 拼接项目编号条件
    If Not IsNull(Me.txtProjectNo) Then
        ' 转义双引号避免输入特殊字符报错
        strSQL = strSQL & "AND Entity_Project.[Project Number] Like ""*" & Replace(Me.txtProjectNo, """", """""") & "*"" "
    End If
    
    ' 拼接多选Sector条件
    If Me.lstQuerySector.ItemsSelected.Count > 0 Then
        strSectorCond = "AND ("
        For Each oItem In Me.lstQuerySector.ItemsSelected
            strSectorCond = strSectorCond & "Entity_Activity.Sector Like ""*" & Replace(Me.lstQuerySector.ItemData(oItem), """", """""") & "*"" OR "
        Next
        ' 去掉末尾多余的OR
        strSectorCond = Left(strSectorCond, Len(strSectorCond) - 4) & ")"
        strSQL = strSQL & strSectorCond
    End If
    
    ' 赋值给结果子窗体/查询，按你实际的结果展示控件名修改
    Me.frmResult.Form.RecordSource = strSQL
    Me.frmResult.Form.Requery
End Sub

方案2：自定义匹配函数（适合不想修改原有查询结构的场景） #

新建一个公共模块，添加如下函数：

Public Function MatchSectors(sectorField As Variant) As Boolean
    Dim oItem As Variant
    ' 列表框未选中时直接返回匹配
    If Forms!Query_Activities!lstQuerySector.ItemsSelected.Count = 0 Then
        MatchSectors = True
        Exit Function
    End If
    ' 字段为空时返回不匹配，如需空值也返回可修改为True
    If IsNull(sectorField) Then
        MatchSectors = False
        Exit Function
    End If
    ' 遍历选中项，任意一项匹配即返回True
    For Each oItem In Forms!Query_Activities!lstQuerySector.ItemsSelected
        If InStr(1, sectorField, Forms!Query_Activities!lstQuerySector.ItemData(oItem), vbTextCompare) > 0 Then
            MatchSectors = True
            Exit Function
        End If
    Next
    MatchSectors = False
End Function

修改原有查询的WHERE条件，把Sector相关的判断替换为：

And MatchSectors(Entity_Activity.Sector) = True

优化提示 #

如果需要避免子串误匹配（比如选中「金融」时不要带出「互联网金融」的记录），可以把模糊匹配的逻辑调整为前后加分号匹配：

' 把原来的Like拼接替换为下面的内容
strSectorCond = strSectorCond & "(Entity_Activity.Sector Like ""*;" & Replace(Me.lstQuerySector.ItemData(oItem), """", """""") & ";*"" " & _
"OR Entity_Activity.Sector Like "";" & Replace(Me.lstQuerySector.ItemData(oItem), """", """""") & """ " & _
"OR Entity_Activity.Sector Like """ & Replace(Me.lstQuerySector.ItemData(oItem), """", """""") & ";*"" " & _
"OR Entity_Activity.Sector = """ & Replace(Me.lstQuerySector.ItemData(oItem), """", """""") & """) OR "

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