HI,
I want to avoid redundancy as much as possible....
We have an access database (2016) containing records for hundreds of movies, with numerous fields.
The fields concerning this question are tblMovies.Director, tblMovies.Writer and tblMovies.Actors (in reality more, but if someone can help me solve this, the rest is similar).
The user can choose to select on the basis of the Director, or the Writer or an Actor. And then (s)he can fill in some the name (or some characters in the name...). A query fills a separate form with the corresponding records.
What I have now:
1. An inputbox asking which field to select: 1=Director, 2= Writer, 3= Actor, ...
2. With "Select case" the procedure selects the appropriate query: there is a query for the Directors, another for the writers, another for the Actors, etc.
3. They are all input-queries, so in the related field/column of the query (Director or Writer or Actor) the criteria-row contains: Like "*" & [Name ?] & "*"
4. the result is the list with all the movierecords containing that name in the choosen field.
But actually all these queries are identical, except for the field/column in the "WHERE"-part of the query. Example: SELECT ...etc FROM etc WHERE (((tblMovies.Actors) Like "*" & [Name ?] & "*")) ORDER etc.
As there are many fields, that makes many long SQL's.
MY QUESTION: is there a way to use the name choosen in the inputbox (see 1. and 2.) in the WHERE part of the query, "replacing" the (tblMovies.Director), (tblMovies.Writer), (tblMovies.Actors), etc. ? I was looking after something as a variable "SelectedField" that can "replace" the (tblMovies.Director), etc.
I have been searching the internet for two days now, but I can't find a solution.
Many thanks in advance for helping me.
I want to avoid redundancy as much as possible....
We have an access database (2016) containing records for hundreds of movies, with numerous fields.
The fields concerning this question are tblMovies.Director, tblMovies.Writer and tblMovies.Actors (in reality more, but if someone can help me solve this, the rest is similar).
The user can choose to select on the basis of the Director, or the Writer or an Actor. And then (s)he can fill in some the name (or some characters in the name...). A query fills a separate form with the corresponding records.
What I have now:
1. An inputbox asking which field to select: 1=Director, 2= Writer, 3= Actor, ...
2. With "Select case" the procedure selects the appropriate query: there is a query for the Directors, another for the writers, another for the Actors, etc.
3. They are all input-queries, so in the related field/column of the query (Director or Writer or Actor) the criteria-row contains: Like "*" & [Name ?] & "*"
4. the result is the list with all the movierecords containing that name in the choosen field.
But actually all these queries are identical, except for the field/column in the "WHERE"-part of the query. Example: SELECT ...etc FROM etc WHERE (((tblMovies.Actors) Like "*" & [Name ?] & "*")) ORDER etc.
As there are many fields, that makes many long SQL's.
MY QUESTION: is there a way to use the name choosen in the inputbox (see 1. and 2.) in the WHERE part of the query, "replacing" the (tblMovies.Director), (tblMovies.Writer), (tblMovies.Actors), etc. ? I was looking after something as a variable "SelectedField" that can "replace" the (tblMovies.Director), etc.
I have been searching the internet for two days now, but I can't find a solution.
Many thanks in advance for helping me.