Can I use a parameter Query to specify the field instead of criter

[Guest]

Actually, I would like to use a form to set up a parameter query, but It
seems that this works only with the Criteria field. I would like a query to
be able to be run mulitple times, each time on a different field from the
same table. I need to use a form to specify the field name before each run.
Just the text 'table.field1' is returned - the table.field1 data is not
referenced.

 

[Allen Browne]

You cannot dynamically change the name of the field in the query.

Where is this query headed? If it is to filter a form, you could build the
Filter string dynamically. If it is for a report, you could build the
WhereCondition for OpenReport.

If you must do it in a query, re-write the SQL property of the QueryDef:
Dim strWhere As String
Const strcStub = "SELECT * FROM Table1 "
Const strcTail = "ORDER BY Table1.Field1;"
If Not (IsNull(Me.WotFieldName) OR IsNull(Me.WotValue)) Then
strWhere = "WHERE ([" & Me.WotFieldName & "] = """ & _
Me.WotValue & """)"
End If
dbEngine(0)(0).QueryDefs("Query1").SQL = strcStub & strWhere & strcTail

Remove the extra quotes if if the field is a Number type, or use # as the
delimiter if it is a Date/Time type.

 

[Jamie Collins]

You cannot dynamically change the name of the field in the query.

If the question is 'Can I use a parameter Query to specify the field'
then the answer is 'You can' e.g.

CREATE TABLE Prices (
USD_price DECIMAL(19, 4) NOT NULL,
GBP_price DECIMAL(19, 4) NOT NULL,
EUR_price DECIMAL(19, 4) NOT NULL
);

CREATE PROCEDURE GetPrice
(arg_currency_code CHAR(3) = 'USD')
AS
SELECT arg_currency_code AS currency_code,
SWITCH(
arg_currency_code = 'USD', USD_price,
arg_currency_code = 'GBP', GBP_price,
arg_currency_code = 'EUR', EUR_price,
TRUE, USD_price
) AS price
FROM Prices;

Jamie.

--