Skip to content

Passing Parameter Values to a Query

Here are a couple of codes for passing parameter values to a query.

Q:Michael O’neill Johnston wrote to ask how to pass a variable (created from a value in a table field) to a parameter query.

A:There are many ways of passing a parameter value to a query.  If you are looking for a code equivalent of specifying criteria, the method I like best is to create (or recreate) the query from a SQL statement, using the variable.  Here is a typical example:

   …

   Set dbs = CurrentDb
   strQuery = “qryExport” & strSet & “Set”
   strSQL = “SELECT Set, Module FROM tblSubjectSets” _
      & ” WHERE Set = ” & Chr$(39) & strSet & Chr$(39) & “;”
   Debug.Print “SQL String: ” & strSQL
   Call DeleteAndRecreateQuery(strQuery, strSQL)
   Set rstExport = dbs.OpenRecordset(strQuery)

   …

 

Sub DeleteAndRecreateQuery(strQuery As String, strSQL As String)
 

On Error Resume Next

 

   ‘Delete old query

   Set dbs = CurrentDb
   dbs.QueryDefs.Delete strQuery

 

On Error GoTo ErrorHandler
  
   ‘Create new query

   Set qdf = dbs.CreateQueryDef(strQuery, strSQL)
  
ErrorHandlerExit:
   Exit Sub

 

ErrorHandler:
   MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
   Resume ErrorHandlerExit

 

End Sub

 

I put the code that does the recreation into a separate procedure, for reusability.

If you are talking about the type of parameter query that actually uses parameters (Parameter objects), then here is some sample code from my book on the DAO Object model:

Private Sub cmdParameters_Click()

 

On Error GoTo ErrorHandler

 

   Dim dbs As DAO.Database
   Dim qdf As DAO.QueryDef
   Dim prmBegin As DAO.Parameter
   Dim prmEnd As DAO.Parameter
   Dim dteFirst As Date
   Dim dteLast As Date
   Dim rst As DAO.Recordset
  
   Set dbs = OpenDatabase(“D:DocumentsNorthwind.mdb”)
   Set qdf = dbs.CreateQueryDef(“”, “PARAMETERS dteStartDate DateTime, ” _
      & “dteEndDate DateTime; SELECT [FirstName] & ‘ ‘ & [LastName] ” _
      & “AS EmployeeName, Orders.OrderID, Orders.OrderDate ” _
      & “FROM Orders INNER JOIN Employees ON Orders.EmployeeID = ” _
      & “Employees.EmployeeID WHERE Orders.OrderDate Between ” _
      & “[dteStartDate] And [dteEndDate] ORDER BY Orders.OrderDate;”)
   Set prmBegin = qdf.Parameters!dteStartDate
   Set prmEnd = qdf.Parameters!dteEndDate
   dteFirst = CDate(InputBox(“Start date”))
   dteLast = CDate(InputBox(“End date”))

 

   ‘Feed new parameter values to recordset and print
   ‘values to the Debug window

   prmBegin = dteFirst
   prmEnd = dteLast
   Set rst = qdf.OpenRecordset(dbOpenForwardOnly)
   Debug.Print “Printing records from ” & prmBegin & ” to ” & prmEnd
  
   Do While Not rst.EOF
      Debug.Print “Employee: ” & rst!EmployeeName
      Debug.Print “Order date: ” & rst!OrderDate
      rst.MoveNext
   Loop
  
   rst.Close
  
ErrorHandlerExit:
   Exit Sub

 

ErrorHandler:
   MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
   Resume ErrorHandlerExit

 

End Sub

About this author