MS-Access / Getting Started

PARAMETERS Declaration

In a desktop database (.accdb), precedes an SQL statement to define the data types of any parameters you include in the query. You can use parameters to prompt the user for data values or to match data values in controls on an open form. (In an SQL Server database, you declare the parameters for a function or procedure as part of the CREATE statement.)


PARAMETERS {[parameter-name] data-type},... ;

Notes If your query prompts the user for values, each parameter name should describe the value that the user needs to enter. For example, [Print invoices from orders on date:] is much more descriptive than [Enter date:]. If you want to refer to a control on an open form, use this format:


To refer to a control on a subform, use this format:


Valid data type entries are shown in Table-3.

Table-3 SQL Parameter Data Types
SQL Parameter Data TypesEquivalent Access Data Type
Char, Text(n)¹, VarCharText
Text¹, LongText, LongChar, MemoMemo
TinyInt, Byte, Integer1Number, Byte
SmallInt, Short, Integer2Number, Integer
Integer, Long, Integer4Number, Long Integer
Real, Single, Float4, IEEESingleNumber, Single
Float, Double, Float8, IEEEDoubleNumber, Double
Decimal, NumericNumber, Decimal
UniqueIdentifier, GUIDNumber, Replication ID
DateTime, Date, TimeDate/Time
Money, CurrencyCurrency
Bit, Boolean, Logical, YesNoYes/No
Image, LongBinary, OLEObjectOLE Object
Text, LongText, LongChar, MemoHyperlink²
Binary, VarBinaryBinary³

¹ Text with a length descriptor of 255 or less maps to the Access Text data type. Text with no length descriptor is a Memo field.

² Internally, Access stores a Hyperlink in a Memo field but sets a custom property to indicate a Hyperlink format.

³ The ACE supports a Binary data type (raw hexadecimal), but the Access user interface does not. If you encounter a non-Access table that has a data type that maps to Binary, you will be able to see the data type in the table definition, but you won't be able to successfully edit this data in a datasheet or form. You can manipulate Binary data in Visual Basic.


To create a parameter query that summarizes the sales and the cost of goods for all items sold in a given month, enter the following (qxmplMonthSalesParameter):

PARAMETERS [Year to summarize:] Short, [Month to summarize:] Short;
SELECT tblProducts.ProductName,
  Format([DateSold],"mmmm"", ""yyyy") AS OrderMonth,
  Sum(tblContactProducts.SoldPrice) AS TotalSales
 FROM tblProducts
  INNER JOIN tblContactProducts
  ON tblProducts.ProductID = tblContactProducts.ProductID
 WHERE (Year([DateSold]) = [Year to summarize:])
  AND (Month([DateSold]) = [Month to summarize:])
GROUP BY tblProducts.ProductName, Format([DateSold],"mmmm"", ""yyyy");
[Previous] [Contents] [Next]