flexible query ?

  • Thread starter Thread starter Davor
  • Start date Start date
D

Davor

Is it possible to make flexible query like this ?

I have a function strSelect() which returns string "Attrib1, Attrib2"

after that I'd like to run this SQL statement :

SELECT strSelect() FROM table1

but Access opens field named Expr1000 with text "Attrib1, Attrib2" in it.
Idea ?


thanks, Davor
 
Davor said:
Is it possible to make flexible query like this ?

I have a function strSelect() which returns string "Attrib1, Attrib2"

after that I'd like to run this SQL statement :

SELECT strSelect() FROM table1

but Access opens field named Expr1000 with text "Attrib1, Attrib2" in it.
Idea ?


thanks, Davor
Click to expand...

If your function strSelect quite literally returns the string "Attrib 1,
Attrib2" then Access has done exactly what you asked it to do. What were
you wanting to happen?
 
I want to choose fields from table and execute sql like :

SELECT Attrib1, Attrib2 FROM Table1

and in another case when function returns "Attrib3, Attrib4" to execute sql
:

SELECT Attrib3, Attrib4 FROM Table1
 
Davor said:
I want to choose fields from table and execute sql like :

SELECT Attrib1, Attrib2 FROM Table1

and in another case when function returns "Attrib3, Attrib4" to execute sql
:

SELECT Attrib3, Attrib4 FROM Table1
Click to expand...

OK, I understand. But, I'm afraid you can't do that. You will need to
build the whole query dynamically, but how you do that depends on the
context. What are you using the query for, and how are you running it?
 
o.k. here is a whole problem ;

I have a large table with 40 fields, containing a lot of different
parameters. I want to give user opportunity to choose only few fields, open
them in a view and then export those fields in Excel table. So I can't make
few standard views because there is a large number of possible combinations
of parameters user may want to examine in different occassions.

(Sorry for my English, I know it looks strange... :o)) )

Davor
 
by the way, I've tried with RunSQL Macro, but it only works with Action
queries (like update, insert, delete) but it doesn't work with SELECT
statement

Davor
 
Concatenate the return value of the function into your SQL string:
Dim strSql As String
strSQL = "SELECT " & strSelect() & " FROM table1;"

Now, I think Brian's question was, What do you want to do with the result?


If you want to change Query1 so it just shows those fields:
CurrentDb().QueryDefs("Query1").SQL = strSQL

If you want to show it in a form:
Forms!Form1.RecordSource = strSQL
though a better solution might be to just set the other controls Visible
property to No.
 
Davor said:
Is it possible to make flexible query like this ?

I have a function strSelect() which returns string "Attrib1, Attrib2"

after that I'd like to run this SQL statement :

SELECT strSelect() FROM table1

but Access opens field named Expr1000 with text "Attrib1, Attrib2" in it.
Idea ?


thanks, Davor
Click to expand...

Allen is correct, I was trying to establish what your intentions were for
this query, given that the one thing you CANNOT do is to create a
stand-alone query object with dynamically determined columns! Another
possibility, as well as those suggested by Allen, is that you are trying to
open a recordset e.g.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String

strSQL = "SELECT " & strSelect() & " FROM table1;"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
 
May I send you an example (180 kb) ? It works with Make table macro but I
haven't experience dealing with recordset.

Davor
 
Brian said:
the one thing you CANNOT do is to create a
stand-alone query object with dynamically determined columns
Click to expand...

That's not necessarily correct. If all the columns were the same data
type, or the specs allow to convert all to the same data type (e.g.
TEXT), then some horrid CASE (equivalent) statements would serve.
Simple example: a table with four text columns and a procedure that
takes two column names as parameters:

CREATE TABLE Brian
(
MyText1 TEXT NULL,
MyText2 TEXT NULL,
MyText3 TEXT NULL,
MyText4 TEXT NULL
)
;
INSERT INTO Brian
(MyText1, MyText2, MyText3, MyText4)
VALUES
(
'This is MyText1',
'This is MyText2',
'This is MyText3',
'This is MyText4'
)
;
CREATE PROCEDURE ProcBrian
(
ArgCol1 TEXT,
ArgCol2 TEXT
) AS
SELECT
SWITCH(
ArgCol1='MyText1',MyText1,
ArgCol1='MyText2',MyText2,
ArgCol1='MyText3',MyText3,
ArgCol1='MyText4',MyText4,
0=0,NULL
) AS Col1,
SWITCH(
ArgCol2='MyText1',MyText1,
ArgCol2='MyText2',MyText2,
ArgCol2='MyText3',MyText3,
ArgCol2='MyText4',MyText4,
0=0,NULL
) AS Col2
FROM Brian
;
EXEC ProcBrian 'MyText2', 'MyTexT4'
;

So it can be done. Why anyone would want to write horrid dynamic sql
is another matter <g>.

Jamie.

--
 
Davor said:
o.k. here is a whole problem ;

I have a large table with 40 fields, containing a lot of different
parameters. I want to give user opportunity to choose only few fields, open
them in a view and then export those fields in Excel table. So I can't make
few standard views because there is a large number of possible combinations
of parameters user may want to examine in different occassions.

(Sorry for my English, I know it looks strange... :o)) )

Davor
Click to expand...

What mechanism are you offering to the users to enable them to choose the
fields they want?
 
Back
Top