Parameter Object

Description

A Parameter object represents a value supplied to a query. The parameter is associated with a QueryDef object created from a parameter query.

Remarks

Parameter objects allow you to change the arguments in a frequently run QueryDef object without having to recompile the query.

Using the properties of a Parameter object, you can set a query parameter that can be changed before the query is run. You can:

Use the Name property to return the name of a parameter.
Use the Value property to set or return the parameter values to be used in the query.
Use the Type property to return the data type of the Parameter object.
Use the Direction property to set or return whether the parameter is an input parameter, an output parameter, or both.

In an ODBCDirect workspace, you can also:

Change the setting of the Type property. Doing so will also clear the Value property.
Use the Direction property to set or return whether the parameter is an input parameter, an output parameter, or both.

Properties

Direction property, Name property, Type property, Value property.

Example

This example demonstrates Parameter objects and the Parameters collection by creating a temporary QueryDef and retrieving data based on changes made to the QueryDef object's Parameters. The ParametersChange procedure is required for this procedure to run.

Sub ParameterX()

    Dim dbsNorthwind As Database
    Dim qdfReport As QueryDef
    Dim prmBegin As Parameter
    Dim prmEnd As Parameter
    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    ' Create temporary QueryDef object with two
    ' parameters.
    Set qdfReport = dbsNorthwind.CreateQueryDef("", _
        "PARAMETERS dteBegin DateTime, dteEnd DateTime; " & _
        "SELECT EmployeeID, COUNT(OrderID) AS NumOrders " & _
        "FROM Orders WHERE ShippedDate BETWEEN " & _
        "[dteBegin] AND [dteEnd] GROUP BY EmployeeID " & _
        "ORDER BY EmployeeID")
    Set prmBegin = qdfReport.Parameters!dteBegin
    Set prmEnd = qdfReport.Parameters!dteEnd

    ' Print report using specified parameter values.
    ParametersChange qdfReport, prmBegin, #1/1/95#, _
        prmEnd, #6/30/95#
    ParametersChange qdfReport, prmBegin, #7/1/95#, _
        prmEnd, #12/31/95#

    dbsNorthwind.Close

End Sub

Sub ParametersChange(qdfTemp As QueryDef, _
    prmFirst As Parameter, dteFirst As Date, _
    prmLast As Parameter, dteLast As Date)
    ' Report function for ParameterX.

    Dim rstTemp As Recordset
    Dim fldLoop As Field

    ' Set parameter values and open recordset from
    ' temporary QueryDef object.
    prmFirst = dteFirst
    prmLast = dteLast
    Set rstTemp = _
        qdfTemp.OpenRecordset(dbOpenForwardOnly)
    Debug.Print "Period " & dteFirst & " to " & dteLast

    ' Enumerate recordset.
    Do While Not rstTemp.EOF

        ' Enumerate Fields collection of recordset.
        For Each fldLoop In rstTemp.Fields
            Debug.Print " - " & fldLoop.Name & " = " & fldLoop;
        Next fldLoop

        Debug.Print
        rstTemp.MoveNext
    Loop

    rstTemp.Close

End Sub

Example (Microsoft Access)

The following example creates a new parameter query and supplies values for the parameters:

Sub NewParameterQuery()
    Dim dbs As Database, qdf As QueryDef, rst As Recordset
    Dim prm As Parameter, strSQL As String

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Construct SQL string.
    strSQL = "PARAMETERS [Beginning OrderDate] DateTime, " _
        & "[Ending OrderDate] DateTime; SELECT * FROM Orders " & _
        "WHERE (OrderDate Between[Beginning OrderDate] " _
        & "And [Ending OrderDate]);"
    ' Create new QueryDef object.
    Set qdf = dbs.CreateQueryDef("ParameterQuery", strSQL)
    ' Supply values for parameters.
    qdf.Parameters![Beginning OrderDate] = #4/1/95#
    qdf.Parameters![Ending OrderDate] = #4/30/95#
    ' Open recordset on QueryDef object.
    Set rst = qdf.OpenRecordset
    rst.MoveLast
    MsgBox "Query returned " & rst.RecordCount & " records."
    rst.Close
    Set dbs = Nothing
End Sub