Using a VBA function in SQL Query

popaddar

New member
Local time
Today, 23:13
Joined
Feb 26, 2015
Messages
6
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
 
you need to declare the function type

Public Function Test1(Tbl As String, Clmn As Integer) as String/Long/Integer

I can't read your code easily so not sure which type you need

If you are going to post code, please use the code tags (on the advanced editor) to preserve indents and spacing
 
other thought is your function needs to be in a module, not a form class module
 
Further to CJ's comments, I think it would be helpful to readers if you gave us a plain English description of what you want a Function to do. That is what is it that this function is suppose to do? Simple English, and perhaps a sample of input and output.
 
Hi

Thank you for your tip. However, I declared the function and also checked that the function is in a module and it still doesn't work. Here my code again if it helpful:

Code:
Public Function Test1(Tbl As String, Clmn As Integer) As Double
 
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
 
End Function



Regards
Dario
 
The function should calculate moving changes with a lag of 3 days. For example, assumed i have the following time-series:

Table: PRICE2
Date Stock1
19.02.2015 2
20.02.2015 5
23.02.2015 4
24.02.2015 5
25.02.2015 7
26.02.2015 9

The function returns:
24.02.2015 1.5 (5/2-1)
25.02.2015 0.4 (7/5-1)
26.02.2015 1.25 (9/4-1)


Thank you.
 
It looks like it's calculating based on 3 business days, since there is no data for 21 and 22 02(FEB). So, you have it working now, or is there an issue?

FYI , here is an indented version of the function based on Smart Indenter ( a free utility). I note there are basically no comments nor any error handling.

Code:
Public Function Test1(Tbl As String, Clmn As Integer) As Double

    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

End Function
 
Yes the code I posted is working so far. If I run ?Test1("PRICE2",1) in the immediate window i get the correct result (PRICE2 is a table in my Database). However, I want to use a SQL query to call the function and do the calculation:

SELECT PRICE2.ID, Test1("PRICE2",1) AS MDiff
FROM PRICE2;

I get 0 values when I run the query and I don't know why.
 
In your SQL string I think it won't recognise the double quotes around PRICE2 , use single quote marks '
Your SQL string should probably be
Code:
sSQL = "SELECT PRICE2.ID, Test1('PRICE2',1) AS MDiff FROM PRICE2;"
 
are you sure you are running the same code to what you have posted - which does not assign anything to Test1 (it's commented out)
 
Yes Test1 = MovingDiff is commented out since I get a error-message there. But I'm not sure whether you neccessarily nead it since the function seems to work in the immediate window. I will have a closer look to this since this might be the issue.

@ Minty: Thank you but this isn't the issue!
 
A function returns a value. It does so by assigning a value to the function Name.
In your function definition it is this commented statement that would return a value
Code:
 'Test1 = MovingDiff
Your use/testing of the function is printing a value to the immediate window.

Try another procedure along the lines of:
Code:
Sub MyTest()
Dim MyTableName as string
Dim MyColumnName as string

MyTableName ="  " '<-----------------a table anme goes here
MyColumnName = " " '<---------------a columnane for your table goes here

debug.print test1(Mytablename,Mycolumnname)

end sub
 
I understand that the issue is in the

Code:
 Test1 = MovingDiff

Statement. Why does this not work?
 
Can a double variable (Test1) be equal an array (MovingDiff())?
 

Users who are viewing this thread

Back
Top Bottom