Hi,
Instead of
SELECT
(select IndexTable.name from IndexTable where IndexTable.value =
STable.number_1 ) AS Field_1,
(select IndexTable.name from IndexTable where IndexTable.value =
STable.number_2 ) AS Field_2,
:
:
:
(select IndexTable.name from IndexTable where IndexTable.value =
STable.number_n ) AS Field_n
FROM STable;
try a more conventionnal syntax:
SELECT i1.name, i2.name, i3.name, ... i_n.name
FROM ((( ... ( STable LEFT JOIN IndexTable As i1 ON
STable.number1=i1.number1)
LEFT JOIN IndexTable As i2 ON STable.Number2=i2.number2)
LEFT JOIN IndexTable As i3 ON STable.Number3=i3.number3)
... )
LEFT JOIN IndexTable As i_n ON STable.NumberN = i_n.NumberN
From that point, I don't understand what you want. If you have a parameter
(or many parameters) in the WHERE clause, or in STable if STable is itself a
query, or in IndexTable, then, in VBA, you can do something like this to
specify each parameter:
===========================================
Dim param as DAO.Parameter
Dim qdf As DAO.QueryDef
Dim db As Database : Set db=CurrentDb
Set qdf= db.QueryDefs(" query name here ")
For each param in qdf.Parameters
set param = ... ' the parameter name is param.Name,
' up to you to specify the value you want for it,
here
Next param
Dim rst As DAO.Recordset
Set rst=qdf.OpenRecordset( ... options, if any, here ... )
=============================================
Hoping it may help,
Vanderghast, Access MVP
devel said:
Thank You Very Much!
It do Work!
There is another question encountered.
That is Calling parameterized query with a parameter generated by the
caller.
If it can go, how can I get through?
Thanks.
The Query is:
SELECT
(select IndexTable.name from IndexTable where IndexTable.value =
STable.number_1 ) AS Field_1,
(select IndexTable.name from IndexTable where IndexTable.value =
STable.number_2 ) AS Field_2,
:
:
:
(select IndexTable.name from IndexTable where IndexTable.value =
STable.number_n ) AS Field_n
FROM STable;
Another solution as follows:
First define a subquery named "GetIndexName":
PARAMETERS IndexValue Short;
SELECT IndexTable.name FROM IndexTable WHERE IndexTable.value =
[IndexValue];
Then the desired query should like:
SELECT
{CALL GetIndexName(STable.number_1)}.name as Field_1,
{CALL GetIndexName(STable.number_1)}.name as Field_2,
:
:
:
{CALL GetIndexName(STable.number_1)}.name as Field_n
FROM STable;
Michel Walsh said:
Hi,
You just insert it as if it was a table. The "outermost" query would see
the parameters too, as field name coming from the embedded query.
Hoping it may help,
Vanderghast, Access MVP
