"IN (UDF())" Delima

[md57780]

I want to create a dynamic IN() clause using a recursive VBA function. My UDF returns all the values I need for my IN() statement, but I cannot get the query to return the correct dataset. Pretty certain its in how I'm calling the function, but I haven't found a solution as of yet (maybe I'm using the wrong search terms).

When I hardcode the values returned from my VBA function into my query, i get the results as expected.

SELECT U.EmpID, U.Manager
FROM tblUsers AS U
WHERE (U.Manager In ("JL69128","JS62907","KS37227","LB38921","MT11414","JM89101"));

When I use the VBA function, zero records are returned every time.

SELECT U.EmpID, U.Manager
FROM tblUsers AS U
WHERE (U.Manager In (AllDirectMgrs("JM89101")));

My Recursive VBA Function

Public Function AllDirectMgrs(EmpID As String, Optional TopLevel As Boolean = True) As String
On Error GoTo ErrorTrap
Dim rs As DAO.Recordset, strSQL As String
Dim DirectList As String
 
    strSQL = "SELECT EmpID FROM tblUsers WHERE (UserActive = True AND IsManager = True AND Manager = """ & EmpID & """);"
 
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
 
    Do While Not rs.EOF
        DirectList = DirectList & """" & rs!EmpID& ""","
        DirectList = DirectList & AllDirectMgrs(rs!EmpID, False)
        rs.MoveNext
    Loop
 
    If TopLevel Then DirectList = DirectList & """" & EmpID & """"
 
    AllDirectMgrs = DirectList
 
ExitFunction:
    Set rs = Nothing
    Exit Function
 
ErrorTrap:
    AllDirectMgrs = ""
    Resume ExitFunction
 
End Function

My alternative approach (which I'm trying to avoid) would be to create a temp table, write the values from the UDF into that function, and use a statement similiar to "IN (SELECT EmpID FROM tableTemp)".

Thanks for any insight you might be able to provide!

 

[Banana]

A common mistake with IN() is to assume that it means "parse whatever I pass in the ()s" when it's actually a compile-time shortcut for longer form of:

WHERE x = a OR x = b OR x = c ...

Thus, IN() cannot be use in a dynamic sense. A common approach, instead is to use an ad hoc query that runs the UDF and embed the result in the string. Something like this for example.

CurrentDb.OpenRecordset("SELECT U.EmpID, U.Manager
FROM tblUsers AS U
WHERE (U.Manager In (" & AllDirectMgrs("JM89101") & "));")

 

[md57780]

Learn something new every day. Now that i know what IN() is actually doing, it makes sense.

Not sure yet if I'll be able to use the ad hoc approach in this particular situation, but it does give me an avenue to think through.

Appreciate the quick reply.