Export (0) Print
Expand All

PARAMETERS Declaration (Microsoft Access SQL)

Last modified: June 29, 2011

Applies to: Access 2013 | Office 2013

In this article
Syntax
Remarks
Example

Declares the name and data type of each parameter in a parameter query.

PARAMETERS name datatype [, name datatype [, …]]

The PARAMETERS declaration has these parts:

Part

Description

name

The name of the parameter. Assigned to the Name property of the Parameter object and used to identify this parameter in the Parameters collection. You can use name as a string that is displayed in a dialog box while your application runs the query. Use brackets ([ ]) to enclose text that contains spaces or punctuation. For example, [Low price] and [Begin report with which month?] are valid name arguments.

datatype

One of the primary Microsoft Access SQL data types or their synonyms.

For queries that you run regularly, you can use a PARAMETERS declaration to create a parameter query. A parameter query can help automate the process of changing query criteria. With a parameter query, your code will need to provide the parameters each time the query is run.

The PARAMETERS declaration is optional but when included precedes any other statement, including SELECT.

If the declaration includes more than one parameter, separate them with commas. The following example includes two parameters:

PARAMETERS [Low price] Currency, [Beginning date] DateTime;

You can use name but not datatype in a WHERE or HAVING clause. The following example expects two parameters to be provided and then applies the criteria to records in the Orders table:

PARAMETERS [Low price] Currency, 
[Beginning date] DateTime; 
SELECT OrderID, OrderAmount
FROM Orders 
WHERE OrderAmount > [Low price] 
AND OrderDate >= [Beginning date];

This example requires the user to provide a job title and then uses that job title as the criteria for the query.

This example calls the EnumFields procedure, which you can find in the SELECT statement example.

Sub ParametersX() 
 
    Dim dbs As Database, qdf As QueryDef 
    Dim rst As Recordset 
    Dim strSql As String, strParm As String 
    Dim strMessage As String 
    Dim intCommand As Integer 
     
    ' Modify this line to include the path to Northwind 
    ' on your computer. 
    Set dbs = OpenDatabase("NorthWind.mdb") 
     
    ' Define the parameters clause. 
    strParm = "PARAMETERS [Employee Title] CHAR; " 
 
    ' Define an SQL statement with the parameters 
    ' clause. 
    strSql = strParm & "SELECT LastName, FirstName, " _ 
        & "EmployeeID " _ 
        & "FROM Employees " _ 
        & "WHERE Title =[Employee Title];" 
     
    ' Create a QueryDef object based on the  
    ' SQL statement. 
    Set qdf = dbs.CreateQueryDef _ 
        ("Find Employees", strSql) 
     
    Do While True 
        strMessage = "Find Employees by Job " _ 
            & "title:" & Chr(13) _ 
            & "  Choose Job Title:" & Chr(13) _ 
            & "   1 - Sales Manager" & Chr(13) _ 
            & "   2 - Sales Representative" & Chr(13) _ 
            & "   3 - Inside Sales Coordinator" 
         
        intCommand = Val(InputBox(strMessage)) 
         
        Select Case intCommand 
            Case 1 
                qdf("Employee Title") = _ 
                    "Sales Manager" 
            Case 2 
                qdf("Employee Title") = _ 
                    "Sales Representative" 
            Case 3 
                qdf("Employee Title") = _ 
                    "Inside Sales Coordinator" 
            Case Else 
                Exit Do 
        End Select 
         
        ' Create a temporary snapshot-type Recordset. 
        Set rst = qdf.OpenRecordset(dbOpenSnapshot) 
 
        ' Populate the Recordset. 
        rst.MoveLast 
             
        ' Call EnumFields to print the contents of the  
        ' Recordset. Pass the Recordset object and desired 
        ' field width. 
        EnumFields rst, 12 
 
    Loop 
     
    ' Delete the QueryDef because this is a 
    ' demonstration. 
    dbs.QueryDefs.Delete "Find Employees" 
     
    dbs.Close 
 
End Sub
Show:
© 2014 Microsoft