Originally Posted by
jerikson
Is there a way to add something in this allowing me to select multiple subjects, multiple instructors for a select number of hours? Kind of like the above excel sheet shows on the original post? I apologize for all the questions.
BR Fire Academy Training Log.zip
To enable you to restrict a query on the basis of multiple values entered as parameters, first add the following module to the databse:
Code:
' Module basInParam
' The functions in this module were published by Microsoft, but
' the article in question is no longer available.
' They are used in this demo database to simulate the use of the
' IN operator in a query, but by accepting a value list as a parameter
' rather than a literal value list as the IN operator requires.
' The parameter in this case is a hidden control in a form
' from which a report is opened.
Option Compare Database
Option Explicit
Function GetToken(stLn, stDelim)
Dim iDelim As Integer, stToken As String
iDelim = InStr(1, stLn, stDelim)
If (iDelim <> 0) Then
stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
stLn = Mid$(stLn, iDelim + 1)
Else
stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
stLn = ""
End If
GetToken = stToken
End Function
'============================================================
' The InParam() function is the heart of this article. When
' the query runs, this function causes a query parameter
' dialog box to appear so you can enter a list of values.
' The values you enter are interpreted as if you
' had entered them within the parentheses of the IN operator.
'============================================================
Function InParam(Fld, Param)
Dim stToken As String
'The following two lines are optional, making queries
'case-insensitive
Fld = UCase(Fld)
Param = UCase(Param)
If IsNull(Fld) Then Fld = ""
Do While (Len(Param) > 0)
stToken = GetToken(Param, ",")
If stToken = LTrim$(RTrim$(Fld)) Then
InParam = -1
Exit Function
Else
InParam = 0
End If
Loop
End Function
You can then call the InParam function in a query as in the following example:
Code:
SELECT
Instructors.Name,
TrainingLog.Date,
Subjects.Subject,
Hours.Hour
FROM
Hours
RIGHT JOIN (
Subjects
RIGHT JOIN (
Instructors
RIGHT JOIN (
TrainingLog
LEFT JOIN TrainingInstructors ON TrainingLog.ID = TrainingInstructors.TrainingID
) ON Instructors.ID = TrainingInstructors.InstructorID
) ON Subjects.ID = TrainingLog.SubjectID
) ON Hours.ID = TrainingLog.HourID
WHERE
InParam (Instructors.Name, [Enter list of instuctors:]) =TRUE;
Try entering the following at the parameter prompt: Ken Scofield,Sophia Syrigos. Note that unlike most value lists the values do not need to be delimited with quote characters. As you'll see the query's result table will be restricted to the two instructors selected. You can of course call the InParam function in the same way on multiple columns in a query to further restrict its results. In a developed application, rather than a simple parameter prompt, it would be possible to select multiple items in a multi-select list box. The attached little demo file illustrates this, though, rather than using the above module, the search criteria are built in code on the basis of the values selected in the list box.
Windows 11 
Office 365
Reply With Quote
