How to use custom VBA function in WHERE IN condition

I am trying to use a custom VBA function to populate the list for the WHERE IN function of an access SQL query however I can’t seem to get it working. A simplified version of the query is shown below

SELECT staffNo, [Staff Member], CoachingStage, 
FROM tblCoachingStages
WHERE CoachingReason IN (getList())

The code for the getList() function is

Function getList() As String

If DCount("[CDP]", "[tblAdmin]", "[CDP] = '" & Environ("username") & "'") = 0 Then
        getList= "'Performance', 'ReDeployment'"
Else
        getList= "'Performance', 'ReDeployment', 'Absence'"
End If

End Function

I have tried playing around with the quotes like missing the leading and end quotes in case this is added by the compiler but I can’t get it working.

I’m trying to avoid using a queryDef to change the SQL of the query as the form loads on the open of the database and the query populates a sub form so if I change the sql on the load event of the form it has already run the query in the background.

Thanks

Answer

I don’t think this is going to work. Your function is returning you a single string value, so the WHERE clause is effectively coming out (depending on the result of your DCOUNT function) as either:

WHERE CoachingReason = "'Performance', 'ReDeployment'"

or

WHERE CoachingReason = "'Performance', 'ReDeployment', 'Absence'"

Maybe you could try

WHERE getList() Like "*" & CoachingReason & "*"