Query asking input for field name?

[quadomatic]

Hey all,

I have a bunch of records in multiple tables. Each record has an identifier. I have another table with the same identifiers, that has some fields which are basically used for yes or no to certain criteria (using 1 for yes and 0 for no).

I want a query that can pull data from each table based on one of those criterion. I was trying to write a query that would work by prompting the user to input a field name, and then the query would pull records limited to those which meet the criteria.

Here's the SQL I wrote:

SELECT [Table_With_Many_Records].*
FROM CriteriaTable, [Table_With_Many_Records]
WHERE [Table_With_Many_Records].id=[CriteriaTable].id AND [CriteriaTable].[]=1;

Is there a way for the user to be able to input that field name? I get it to prompt for input, but it tells me the expression is typed incorrect or is too complex to be evaluated. Any help?

 

[jzwp22]

The field name itself cannot be supplied via a parameter in a query, but you can use an unbound form and then execute VBA code that would dynamically create a query based on selections made by the user.

 

[MSAccessRookie]

Hey all,

I have a bunch of records in multiple tables. Each record has an identifier. I have another table with the same identifiers, that has some fields which are basically used for yes or no to certain criteria (using 1 for yes and 0 for no).

I want a query that can pull data from each table based on one of those criterion. I was trying to write a query that would work by prompting the user to input a field name, and then the query would pull records limited to those which meet the criteria.

Here's the SQL I wrote:

SELECT [Table_With_Many_Records].*
FROM CriteriaTable, [Table_With_Many_Records]
WHERE [Table_With_Many_Records].id=[CriteriaTable].id AND [CriteriaTable].[]=1;

Is there a way for the user to be able to input that field name? I get it to prompt for input, but it tells me the expression is typed incorrect or is too complex to be evaluated. Any help?

I do not believe that you can do what you are trying to do. What you can do, is create a Combo Box/List Box/DropDown Box that contains possible choices, and then update the Query based on the result of the user selecting a choice on the list. If you change your query to the Join/On Format, then you could start with the Query below, and then use VBA to add a Where Statement whenever the user selects a filter.

[B][COLOR=blue]SELECT [Table_With_Many_Records].*[/COLOR][/B]
[B][COLOR=blue]FROM CriteriaTable INNER JOIN [Table_With_Many_Records][/COLOR][/B]
[B][COLOR=blue]ON [Table_With_Many_Records].id=[CriteriaTable].id[/COLOR][/B] 
 
[COLOR=seagreen][B]WHERE [CriteriaTable].[ { Value Selected by the user } ]=1[/B]
[COLOR=black]

[/COLOR]

[/COLOR]