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

 

[PHV]

I think SQL can't handle arrays.
You may consider something like this:

Public Function returnArray(score As Integer, idx 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(idx - 1)
End Function

And in the query grid:
Expr1: returnArray(42, 1)
Expr2: returnArray(42, 2)
Expr3: returnArray(42, 3)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[irishjoe]

Thanks for your reply.

I am trying to minimise the accessing of the tables because it is slowing my query down a lot.

The thing is I have a 14 by 5 table which is generated by 5 scores, that can be filled quite quickly via VBA, but if I do it your way, it is chronically slow. I have written a few functions that fill a 2D array very quickly but I don’t know if it was possible to send an array to a query.

The way it used to be done, when using non-calculated values, it generated all the values “on current” which means it just did something like “me.text123.value = array(123)”.

I take it access can’t do what I want it to?