Query Array

[irishjoe]

I was wondering if this was possible or not.
Can I write a function like the one below, which will accept a value and return many values?
I would like to set various columns from one score. If I have something like “Expr1: returnArray(42)” in the query, can the function return the 3 values and put them in separate columns?
If anyone has any ideas it would be greatly appreciated because I am re-writing my software to have no calculated values.

Public Function returnArray(score As Integer) As Variant
    Dim arrMatrix(2) As String
    'some calculations here to set the arrMatrix array.
    arrMatrix(0) = "Joe"
    arrMatrix(1) = "is"
    arrMatrix(2) = "Stuck"
    returnArray = arrMatrix()
    
End Function

 

[Uncle Gizmo]

I'm not sure I understand your question. If you are trying to return a variable number of columns, sometimes two, sometimes four in your query, then that is not possible. However you could look at a "cross tab" query, That may give you the functionality you are looking for.

 

[irishjoe]

Yeah, thats what I was trying to do.
I'll have a look into it. Cheers.