Pass multiple string values to a parameter in a query

I'm trying to run an Access parameter query and pass it a list of values to use in a NOT IN clause.

• When I pass the values in a parameter it seems to ignore it and
  return all records.
• When I pass the values as a variable embedded
  in the SQL it works correctly and returns the four records not
  mentioned in the NOT IN clause.

So my question is: How do I pass multiple string values in a parameter?

The two tables used in the query are:

HourlyRateTypes (only RateType is used from this table).

| ID | RateType | ShowOnTimeSheet | SortOrder | SuggestCalculation | Enabled | IsBaseRate |
|----|----------|-----------------|-----------|--------------------|---------|------------|
| 1  | Normal   | Yes             | 1         |                    | Yes     | Yes        |
| 2  | Flat     | Yes             | 2         | 1                  | Yes     | No         |
| 3  | x 1.5    | Yes             | 3         | 1.5                | Yes     | No         |
| 4  | x 2      | Yes             | 4         | 2                  | Yes     | No         |
| 5  | x 3      | Yes             | 5         | 3                  | Yes     | No         |
| 6  | Holiday  | Yes             | 6         | 1                  | Yes     | No         |
| 7  | Sick     | Yes             | 7         | 1                  | Yes     | No         |
| 8  | Furlough | No              | 8         | 1                  | No      | No         |

and RoleHourlyRates

| ID | RoleID | RateType | Rate   | EmployerID |
|----|--------|----------|--------|------------|
| 1  | 1      | 1        | £10.15 | 1          |
| 2  | 1      | 2        | £10.15 | 1          |
| 3  | 1      | 3        | £15.23 | 1          |
| 4  | 1      | 4        | £20.30 | 1          |
| 5  | 1      | 5        | £30.45 | 1          |
| 6  | 1      | 6        | £10.15 | 1          |
| 7  | 1      | 7        | £10.15 | 1          |
| 8  | 1      | 8        | £10.15 | 1          |

This version of the code returns all records rather than those not appearing in the ExcludeRecs parameter.

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""" 'Returns "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 

This code returns the correct records - excluding Normal, x 1.5, x 2 and x 3:

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""" 'Returns "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

Flat           10.15 
Holiday        10.15 
Sick           10.15 
Furlough       10.15 

You should define an individual parameter for each item in exclusion list.
Try something like this:

DIM ExclusionList , ParamsList() As String , i As Integer
ExclusionList= ARRAY("Normal" , "x 1.5" , "x 2" , "x 3")
REDIM ParamsList(UBOUND(ExclusionList))

FOR i= 0 To UBOUND(ExclusionList)
    ParamsList(i)= "@PRM" & TRIM(i+1) & " TEXT(255) "
NEXT

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.CreateQueryDef("", _
          "PARAMETERS RoID Long , EmpID Long , " & JOIN(ParamsList , ",") & ";" & _
          "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 (" & REPLACE(JOIN(ParamsList , ",") , " TEXT (255) " , VBNULLSTRING) & ")")

qdf.Parameters("RoID") = pRoleID
qdf.Parameters("EmpID") = pEmployerID
FOR i= 0 To UBOUND(ParamsList)
    qdf.Parameters("PRM" & TRIM(i+1))= ExclusionList(i)
NEXT

PS:
It's better to use RateID's instead of Rate Text.

EDIT:
The SQL text of the qdf query is as following:

PARAMETERS RoID Long , EmpID Long , @PRM1 TEXT(255) ,@PRM2 TEXT(255) ,@PRM3 TEXT(255) ,@PRM4 TEXT(255) ;
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 (@PRM1,@PRM2,@PRM3,@PRM4)