genehead
asked on
Indirect field reference in SQL statement: Possible??
WITHOUT using VBA and only using Access 2000, can I create a two-table query where the contents of a one field in one table can become the column name or field name in another.
TableOne has the following fields:
Field1 Text 10
Field2 Text 10
Field3 Text 10
TableTwo has one record and one field:
FieldName Text 10 <- Holds the value "Field2"
I want to select only Field2 from table one. I've tried the Eval() function without success:
select eval([TableTwo]![FieldName ]) from TableOne
Thanks - gene
TableOne has the following fields:
Field1 Text 10
Field2 Text 10
Field3 Text 10
TableTwo has one record and one field:
FieldName Text 10 <- Holds the value "Field2"
I want to select only Field2 from table one. I've tried the Eval() function without success:
select eval([TableTwo]![FieldName
Thanks - gene
do you mean like this?
SELECT (SELECT tbl2.fld1 FROM tbl2) FROM tbl1;
this would give you the value of tbl2.fld1 the number of records you have in tbl1.
if you need the values of tbl1 that are in the field that has the name in tbl2.fld1 then you will have to compose it in VBA, there is no escaping it.
SELECT (SELECT tbl2.fld1 FROM tbl2) FROM tbl1;
this would give you the value of tbl2.fld1 the number of records you have in tbl1.
if you need the values of tbl1 that are in the field that has the name in tbl2.fld1 then you will have to compose it in VBA, there is no escaping it.
Why don't you want to code in VBA and build the sql string on the fly??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Accepting pbryan's comment as answer. This is then kind of solution I was looking for. I thought of UNION but didn't take it to the crosstab stage. Good thinking pbryan. Thanks - Gene
Remains my Q: Why ?
Nic;o)