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
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