Use criteria in field for fieldname in query

[RW]

I have a basic query to add records to a table. A table in this query
contains numerous fields two of which are named "Criteria Field" and
"Criteria". I need to use the values from these fields as the field and
criteria in the query. In other words, lets assume the Criteria Field =
"[Customers].[Hobby]" and the criteria field = "Coin Collecting". The table
that contains "Criteria Field" and "Criteria" is joined to the Customer table
on another field. How can I put the criteria field in the top row and the
criteria in the bottom row of the design view?

Some iterations of the query will not have information in the fields named
"Criteria Field" and "Criteria". If necessary in these cases, I can use
[Customers].[Name] and have the criteria Like '*'.

Thanks

 

[John W. Vinson]

I have a basic query to add records to a table. A table in this query
contains numerous fields two of which are named "Criteria Field" and
"Criteria". I need to use the values from these fields as the field and
criteria in the query. In other words, lets assume the Criteria Field =
"[Customers].[Hobby]" and the criteria field = "Coin Collecting". The table
that contains "Criteria Field" and "Criteria" is joined to the Customer table
on another field. How can I put the criteria field in the top row and the
criteria in the bottom row of the design view?

Some iterations of the query will not have information in the fields named
"Criteria Field" and "Criteria". If necessary in these cases, I can use
[Customers].[Name] and have the criteria Like '*'.

Thanks

You'll need to write VBA code to construct the SQL of the query. Parameters
only apply to values, not to fieldnames. Do you have complete confidence that
the criteria will in fact be valid? This seems like a pretty complicated way
to do things!

John W. Vinson [MVP]

 

[RW]

Thanks for getting back to me. Obviously my example is simplified. I wasn't
even thinking of using parameters. I have been trying to use a dlookup for
the "Criteria Field" in the field in the query and a dlookup for the
"Criteria" in the criteria part of the query. It seems that the data in the
Criteria Field gets passed as literal text and isn't related to the table and
field name in the query. In other words, the query returns
"[Customers].[Hobby] for every returned record instead of the value of that
field in the joined table.

I'll look into trying to create the SQL.

I have a basic query to add records to a table. A table in this query
contains numerous fields two of which are named "Criteria Field" and
"Criteria". I need to use the values from these fields as the field and
criteria in the query. In other words, lets assume the Criteria Field =
"[Customers].[Hobby]" and the criteria field = "Coin Collecting". The table
that contains "Criteria Field" and "Criteria" is joined to the Customer table
on another field. How can I put the criteria field in the top row and the
criteria in the bottom row of the design view?

Some iterations of the query will not have information in the fields named
"Criteria Field" and "Criteria". If necessary in these cases, I can use
[Customers].[Name] and have the criteria Like '*'.

Thanks

You'll need to write VBA code to construct the SQL of the query. Parameters
only apply to values, not to fieldnames. Do you have complete confidence that
the criteria will in fact be valid? This seems like a pretty complicated way
to do things!

John W. Vinson [MVP]