Use input to determine which field to choose in SQL-WHERE (1 Viewer)

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.

since you already has VBA, then substitute the fieldname using VBA:

I suggest you use combo boxes instead of input boxes to guide the user.
You may even find cascading combos useful.

Have a look at these example databases
Multiple Group and Filter
Cascading Combo boxes

Hi Arnelgp,


many thanks for your very quick and helpful answer !


Maybe it has to do with the acces-version, but with very little adjustments it now works, thank you.

• I used a second inputbox, because the SQL with "[Name ?]" doesn't work in my module, I don't know for what reason...

• Altough the SQL-version of the query defines indeed the field to look at as"tblMovies.Director", etc.I had to drop "tblMovies" and keep only "Director" etc.

So, here is my abreviated Function to do that:

Public Function PrintSelect()
Dim selInt As Integer, selTxt As String
Dim strSQL As String
Dim qd As DAO.QueryDef
On Error GoTo WhatError

selInt = InputBox("Field to select ?" & vbCrLf & _
"1 Director" & vbCrLf & _
"2 Actor" & vbCrLf & _
"3 Autor/Writer" & vbCrLf, & _
1, 1)

selTxt = InputBox("What’s the name ? ")

strSQL = "SELECT * FROM tblMovies WHERE (p% Like '*" & [selTxt] & "*')"

Select Case selInt
Case 1
strSQL = Replace(strSQL, "p%", "Director")
Case 2
strSQL = Replace(strSQL, "p%", "Actors")
Case 3
strSQL = Replace(strSQL, "p%", "Writing")
End Select

Set qd = CurrentDb.QueryDefs("qrySel")
qd.SQL = strSQL
Set qd = Nothing
DoCmd.OpenQuery "qrySel"

Stopnow:
Exit Function
WhatError:
MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical
Resume StopNow

End Function
[FONT=&quot]
[/FONT]
This thus works well!
baudi

Isladogs,
thanks also for your suggestion. I intended to indeed switch to use a single dialogbox with one combox with the list of fields to choose from and one textbox to fill in the name (or some characters of) .
That's for one of the coming days, thanks!

With that approach, you cannot return movies where the director was Clint Eastwood AND one of the stars was Morgan Freeman....
That is OK?

Hi Micron,


you are right, but actually what I let see above is only part of what I already have and anyhow, the database I am working on is far from finished. I intend to use the query to populate a listview and I have already made, years ago, the necessary bits of soft to make all kind of searches possible, including of course combinations as you write about. But thanks for your correct remark/question.

But what was new for me was the approach of arnelgp, because, I don't know why, I never saw that possibility to change/update etc queries from vba...

That solution looks like a fair bit of unnecessary code but more importantly perhaps, still limits you to one choice. But if you're happy with that, then who am I to argue a point?