如何向Access参数查询的NOT IN子句传递多字符串值？

我在运行Access参数查询时遇到了问题：

• 当通过参数向NOT IN子句传递一组值时，查询会忽略该参数，返回所有记录
• 直接将值嵌入SQL语句时，查询正常工作，返回排除指定值后的4条记录

用到的表结构 #

HourlyRateTypes（仅使用RateType字段）

RoleHourlyRates

失败的参数传递代码 #

Public Sub ParamQueryTest()

    Dim pEmployerID As Long
    pEmployerID = 3
    
    Dim pRoleID As Long
    pRoleID = 1
    
    Dim pExclusionText As String
    pExclusionText = """Normal"", ""x 1.5"", ""x 2"", ""x 3""" '生成字符串："Normal", "x 1.5", "x 2", "x 3"
    
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.CreateQueryDef("", _
        "PARAMETERS ExcludeRecs Text (255), RoID Long, EmpID Long; " & _
        "SELECT HRT.RateType, Rate " & _
        "FROM   HourlyRateTypes HRT INNER JOIN RoleHourlyRates RHR ON HRT.ID = RHR.RateType " & _
        "WHERE  RHR.RoleID = RoID AND RHR.EmployerID = EmpID " & _
        "AND HRT.RateType NOT IN (ExcludeRecs)")
    qdf.Parameters("RoID") = pRoleID
    qdf.Parameters("EmpID") = pEmployerID
    qdf.Parameters("ExcludeRecs") = pExclusionText
    
    Dim rst As DAO.Recordset
    Set rst = qdf.OpenRecordset
    
    With rst
        If Not .BOF And Not .EOF Then
            Do
                Debug.Print .Fields("RateType"), .Fields("Rate")
                .MoveNext
            Loop While Not .EOF
        End If
    End With
    
    rst.Close
    Set rst = Nothing
    qdf.Close
    Set qdf = Nothing
    
End Sub  

输出结果（返回所有记录）：

Normal         10.15 
Flat           10.15 
x 1.5          15.23 
x 2            20.3 
x 3            30.45 
Holiday        10.15 
Sick           10.15 
Furlough       10.15 

成功的直接嵌入代码 #

Public Sub ParamQueryTest2()

    Dim pEmployerID As Long
    pEmployerID = 3
    
    Dim pRoleID As Long
    pRoleID = 1
    
    Dim pExclusionText As String
    pExclusionText = """Normal"", ""x 1.5"", ""x 2"", ""x 3""" '生成字符串："Normal", "x 1.5", "x 2", "x 3"
    
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.CreateQueryDef("", _
        "PARAMETERS RoID Long, EmpID Long; " & _
        "SELECT HRT.RateType, Rate " & _
        "FROM   HourlyRateTypes HRT INNER JOIN RoleHourlyRates RHR ON HRT.ID = RHR.RateType " & _
        "WHERE  RHR.RoleID = RoID AND RHR.EmployerID = EmpID " & _
        "AND HRT.RateType NOT IN (" & pExclusionText & ")")
    qdf.Parameters("RoID") = pRoleID
    qdf.Parameters("EmpID") = pEmployerID
    
    Dim rst As DAO.Recordset
    Set rst = qdf.OpenRecordset
    
    With rst
        If Not .BOF And Not .EOF Then
            Do
                Debug.Print .Fields("RateType"), .Fields("Rate")
                .MoveNext
            Loop While Not .EOF
        End If
    End With
    
    rst.Close
    Set rst = Nothing
    qdf.Close
    Set qdf = Nothing
    
End Sub

输出结果（返回排除后的4条记录）：

Flat           10.15 
Holiday        10.15 
Sick           10.15 
Furlough       10.15 

问题原因 #

Access的参数查询中，单个文本参数会被视为单一完整字符串，而非多个值的集合。也就是说，NOT IN (ExcludeRecs)实际是在判断RateType是否不等于整个字符串"Normal", "x 1.5", "x 2", "x 3"，显然没有任何记录符合这个条件，所以返回所有数据。

解决方案 #

方案1：使用临时表存储排除值

通过临时表存储需要排除的每个值，再在查询中关联临时表实现筛选，这是最稳妥的方法：

Public Sub ParamQueryWithTempTable()
    Dim pEmployerID As Long, pRoleID As Long
    pEmployerID = 3
    pRoleID = 1
    
    ' 创建临时表（若已存在则先删除）
    On Error Resume Next
    CurrentDb.Execute "DROP TABLE TempExclusions"
    On Error GoTo 0
    CurrentDb.Execute "CREATE TABLE TempExclusions (RateType Text(255))"
    
    ' 插入要排除的值（注意转义单引号）
    Dim exclusions As Variant, excl As Variant
    exclusions = Array("Normal", "x 1.5", "x 2", "x 3")
    For Each excl In exclusions
        CurrentDb.Execute "INSERT INTO TempExclusions (RateType) VALUES ('" & Replace(excl, "'", "''") & "')"
    Next
    
    ' 构建参数查询
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.CreateQueryDef("", _
        "PARAMETERS RoID Long, EmpID Long; " & _
        "SELECT HRT.RateType, Rate " & _
        "FROM HourlyRateTypes HRT INNER JOIN RoleHourlyRates RHR ON HRT.ID = RHR.RateType " & _
        "WHERE RHR.RoleID = RoID AND RHR.EmployerID = EmpID " & _
        "AND HRT.RateType NOT IN (SELECT RateType FROM TempExclusions)")
    qdf.Parameters("RoID") = pRoleID
    qdf.Parameters("EmpID") = pEmployerID
    
    ' 读取并输出结果
    Dim rst As DAO.Recordset
    Set rst = qdf.OpenRecordset
    With rst
        If Not .BOF And Not .EOF Then
            Do
                Debug.Print .Fields("RateType"), .Fields("Rate")
                .MoveNext
            Loop While Not .EOF
        End If
    End With
    
    ' 清理资源
    rst.Close
    Set rst = Nothing
    qdf.Close
    Set qdf = Nothing
    CurrentDb.Execute "DROP TABLE TempExclusions"
End Sub

方案2：使用自定义拆分函数

通过VBA自定义函数将逗号分隔的字符串拆分成数组，Access会将数组作为多值参数处理：
首先创建拆分函数：

Public Function SplitExclusions(inputStr As String) As Variant
    Dim arr As Variant
    arr = Split(inputStr, ",")
    ' 去除每个元素的引号和前后空格
    Dim i As Integer
    For i = LBound(arr) To UBound(arr)
        arr(i) = Trim(Replace(arr(i), """", ""))
    Next
    SplitExclusions = arr
End Function

然后修改查询代码：

Public Sub ParamQueryWithSplitFunction()
    Dim pEmployerID As Long, pRoleID As Long
    pEmployerID = 3
    pRoleID = 1
    
    Dim pExclusionText As String
    pExclusionText = """Normal"", ""x 1.5"", ""x 2"", ""x 3"""
    
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.CreateQueryDef("", _
        "PARAMETERS ExcludeRecs Text(255), RoID Long, EmpID Long; " & _
        "SELECT HRT.RateType, Rate " & _
        "FROM HourlyRateTypes HRT INNER JOIN RoleHourlyRates RHR ON HRT.ID = RHR.RateType " & _
        "WHERE RHR.RoleID = RoID AND RHR.EmployerID = EmpID " & _
        "AND HRT.RateType NOT IN (SplitExclusions(ExcludeRecs))")
    qdf.Parameters("RoID") = pRoleID
    qdf.Parameters("EmpID") = pEmployerID
    qdf.Parameters("ExcludeRecs") = pExclusionText
    
    Dim rst As DAO.Recordset
    Set rst = qdf.OpenRecordset
    With rst
        If Not .BOF And Not .EOF Then
            Do
                Debug.Print .Fields("RateType"), .Fields("Rate")
                .MoveNext
            Loop While Not .EOF
        End If
    End With
    
    rst.Close
    Set rst = Nothing
    qdf.Close
    Set qdf = Nothing
End Sub

内容的提问来源于stack exchange，提问作者Darren Bartrup-Cook