VBA and SQL - using functions

[Statistician]

Dear all

I am currently running SQL in VBA (MS Access).
I am trying to pass a date field from the SQL SELECT statement to a function and to return a new date based on the function.

The code is as follows:

"SELECT [TRADEDATE],myfunction(TRADEDATE) FROM myTable"

TRADE DATE is a field from a table.

I am trying to pass the TRADEDATE to a function where the function returns a new date.

The basic question I am trying to ask is whether I can pass a field from an SQL SELECT statement to another function in code?

Thanks for any offers of help!!!!

 

[RickSpr]

Yes, this is possible. Have you tried it? If so, what kind of problem are you having?

Note: "myfunction" must be defined as a Public Function in a standard module. Rick Sprague

 

[cmmrfrds]

A select statement is prepared 1 time and sent to the SQL engine, either Jet or some backend engine, which sends back a resultset. I don't believe Access would then apply your function to the returned resultset. You can use a function where it only needs to be applied once in the preparation of the SQl. For instance, in the where clause.
"SELECT [TRADEDATE] FROM myTable where [TRADEDATE] = myfunction(TRADEDATE) "

 

[RickSpr]

Cmmrfrds, this is true in other SQLs, but when Jet is running under Access, the VBA functions are available to the SQL engine, and operate just like built-in SQL functions. The prepare of the SQL statement doesn't replace the function reference with a value; it includes the function reference in the prepared statement, so that the function gets called for each row at SQL execution time.

There are certain cases where this doesn't work in Access, and Statistician's case might be one of them, but it does work in Access query objects, for example. Rick Sprague

 

[cmmrfrds]

Thanks Rick, that is good to know, but I wouldn't do it since it would limit the options on the backend. Statistician should post the function. There is probably a way to accomplish what is needed in SQL or even ADO which has a lot of power and is available on Microsoft OS's.