How to make a query in which the SQL sentence is created by a function

  • Thread starter Thread starter Jordi Pocurull
  • Start date Start date
J

Jordi Pocurull

I tried to put in the SQL mode
"= myfunction () " and the program responds: Select sentence missing.
Otherwise,
"SELECT myfunction() AS Expr1;" and the programs responds: Just a record
with the SQL sentence by itself.
 
I tried to put in the SQL mode
"= myfunction () " and the program responds: Select sentence missing.
Otherwise,
"SELECT myfunction() AS Expr1;" and the programs responds: Just a record
with the SQL sentence by itself.
Click to expand...

A SQL query is just that - a query written in the SQL language. The
text string

=MyFunction()

is not a valid SQL statement (even if MyFunction returns a text string
value).

Could you step back a bit and explain the context? What are you trying
to accomplish, and under what circumstances?
 
Hi John.
I need to join two queries, the first one is a UNION query that is quite
complex, and I create it in a module with a Funcion, in that way if I
execute the funcion it returns the UNION query:

In that way if I write:
Dim strSQL as string
strSQL=MyFunction()

Then:

strSQL="SELECT ....... UNION ..... SELECT ..... UNION...."

Now I want to join that query with anoter one and use the result in a
report. I tried to create that last query via the Query Wizard, but how to
select the query created in the module. Then I tried to create an
intermediate query that returns just the query created with the function.

How do you solve my problem.

I appreciate a lot your interest.
 
Dim strSQL as string
strSQL=MyFunction()

Then:

strSQL="SELECT ....... UNION ..... SELECT ..... UNION...."

Now I want to join that query with anoter one and use the result in a
report. I tried to create that last query via the Query Wizard, but how to
select the query created in the module. Then I tried to create an
intermediate query that returns just the query created with the function.

How do you solve my problem.
Click to expand...

Use the CreateQuerydef method:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Set db = CurrentDb
Set db = CurrentDb
Set qd = db.CreateQuerydef("MyNewQuery", MyFunction())

This will create a new query (visible in the queries window, etc.)
named MyNewQuery using your generated SQL.
 
Thank you very much.
It works properly.

But I had to activate DAO (Wich I thought that was obsolet) Isn't possible
to obtain the same result with ADO?

Jordi Pocurull
 
Back
Top