Use criteria in field for fieldname in query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RW - 26 Nov 2007 17:18 GMT

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

Reply to this Message

John W. Vinson - 26 Nov 2007 21:15 GMT

>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
[quoted text clipped - 10 lines]
>
>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]

Reply to this Message

RW - 26 Nov 2007 22:14 GMT

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
[quoted text clipped - 17 lines]
>
> John W. Vinson [MVP]

Reply to this Message