Using a VBA function in SQL Query

Hi



I'm trying to use a VBA function that I specified myself in a SQL query.



My function is:



Public Function Test1(Tbl As String, Clmn As Integer)



Dim MyDb As dao.Database

Dim Tbldef As dao.TableDef

'Dim Tbl As String

Dim ColName As String

'Dim Clmn As Integer

Dim Que1 As dao.Recordset

Dim SqlStr1 As String



If MyDb Is Nothing Then

Set MyDb = CurrentDb()

End If



'Tbl = "PRICE2"

'Clmn = 1

Set Tbldef = MyDb.TableDefs(Tbl)

ColName = "[" & Tbldef.Fields(Clmn).Name & "]"

SqlStr1 = "SELECT " & ColName & " FROM " & Tbl

Set Que1 = MyDb.OpenRecordset(SqlStr1)

Dim dim1 As Integer

Que1.MoveLast

dim1 = Que1.RecordCount

Que1.MoveFirst

Dim MyArray1()

ReDim MyArray1(dim1)

Dim MovingDiff()

ReDim MovingDiff(dim1)



For i = 0 To dim1 - 1

MyArray1(i) = Que1.Fields(ColName)

On Error Resume Next

Que1.MoveNext

Next i



For i = 0 To dim1 - 1

MovingDiff(i) = MyArray1(i + 3) / MyArray1(i) - 1

On Error Resume Next

Que1.MoveNext

Next i



'Test1 = MovingDiff

For Each Item In MovingDiff

Debug.Print Item

Next

Test1 = MovingDiff()



End Function



I provide 2 parameters to the function: Tbl which contains time-series of different stocks and Clmn where I tell the function for which column I want the calculation to be done.



If I test the function in the VBA immediate window i get the correct result. However, if I run the following SQL Query in MS Access it doesn't return me any values:



SELECT PRICE2.TK, Test1("PRICE2",1) AS MDiff

FROM PRICE2;



Can someone tell me please what I'm doing wrong?



Thank you very much in advance.



Regards

Dario

Using a VBA function in SQL Query