Passing an array through an SQL query to a function

[Access9001]

I want to, from an SQL query, select something like:

myfunc(('1','5','7'))

and then have function myfunc(array()) be able to manipulate the passed array. Possible?

 

[ajetrumpet]

i dont think you can pass array arguments, but what you can do is pass the arguments, then in the function(), declare a variable length array (dynamic one) and loop through your arguments using a common identifier and throw them into the array elements. from there, do what you want with them and equal the output value to the function to give it back to the sql statement. like: myfunction() = output value

 

[ChrisO]

MyTotal: MyFunc([ChartsDataPK],'1','5','7')

Public Function MyFunc(ParamArray MyValues() As Variant) As Variant
    Dim lngIndex As Long
    Dim strTotal As String
    
    [color=green]' Skip the passed PK[/color]
    For lngIndex = LBound(MyValues) + 1 To UBound(MyValues)
        MsgBox MyValues(lngIndex)
        strTotal = strTotal & MyValues(lngIndex)
    Next lngIndex
    
    MyFunc = strTotal

End Function