User defined function in ACCESS query PROBLEM

[wascom]

I am trying to use the function (enclosed in another function), which I created in VBA in one Query, but I constantly get the Error - Undifined Function. I think there is a problem about ARRAY in this function, but it works as expected in VBA, not in Query.
There is a small example of what I what I am trying to do:

Function TestFunction(ParamArray TestArray()) As Double()
Dim i%
ReDim TempArr(UBound(TestArray)) As Double
For i = 0 To UBound(TestArray)
TempArr(i) = TestArray(i)
Next i
TestFunction = TempArr
End Function

Function Konvert(TempArray() As Double) As String
Dim k%
Dim strng As String
For k = 0 To UBound(TempArray)
strng = strng + CStr(TempArray(k)) + ";"
Next
Konvert = Left(strng, Len(strng) - 1)
End Function

QUERY:
Test: Konvert(TestFunction(0,5,10,20,30,40,100))

The result should be as String: "0,5,10,20,30,40,100"


It works withing VBA, but not in a query.

Many thanks for help.

 

[MichaelRed]

Try


strng = strng + CStr(TempArray(k)) & ";"
???





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MichaelRed]

Actually, on review, the issue is more of the actual call. Your syntax is to have the call to (whatever) as a calculated field in a query - but you seem to trying to call the test functionwhich -in turn calls "Kionvert". There appears to be NO need (or any pratical use) for the test function in the query, so:


Test: Konvert (0, 5, 10, 20, 30, 40, 100)

but only after changing the declaration of Konvert per the following.

Partially, the confusion stems from the fact that Paramarray variables cannot be declared with type cast -which you hhave avoided in the test fct with the ommission. in Konvert, the explicit type case of the array creates the necessity for some wrapper function so that a 'real' array can be passed. BUT, simply declaring the input to Konvert as a param array and permitting the type cast to default to (or be explicitly declared) as variant makes the test fct unnecessary.






MichaelRed
m.red@att.net

Searching for employment in all the wrong places