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

D

DMachart

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.
 
A

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.
 
Top