Call a Function via control element in SQL (1 Viewer)

[Arvidsson]

Hello,

is it possible to call a function in a query with the input of a control element?

I have a form, and there is a list box in which I can choose the calculate method. In the query, a field should calculated with the choosen function.

Example:

SELECT tblTest.price, tblTest.Date, forms![formtest]![lstChosenFunction](tblTest.price, 5) As Calc
...

The function has the same name as I can choose in the listbox. But, if I run the query a error appears.

"Function forms![formtest]![lstChosenFunction] could not be found"

I would be very glad if someone has an idea how I can call a function in SQL with the input of an listbox.

Thank you very much in advance!

 

[pr2-eugin]

It cannot be done as you hoped to do this. I think this would involve created QueryDef object and manipulating the SQL.

 

[Arvidsson]

It cannot be done as you hoped to do this. I think this would involve created QueryDef object and manipulating the SQL.

Can you give me an example how I could do this?
Thank you very much

 

[pr2-eugin]

Something like,

Public Sub modifySQL()
    Dim qObj As DAO.QueryDef
    
    Set qObj = CurrentDb.QueryDef("yourQueryName")
    qObj.SQL = "SELECT tblTest.price, tblTest.[Date], " & Me.yourListBoxName & "(tblTest.price, 5) As Calc " & _
               "FROM tblTest"
End Sub

More info : http://msdn.microsoft.com/en-us/library/office/ff194892.aspx

 

[Arvidsson]

Thank you very much pr2-eugin.

I will test it