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.)

Syntax

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:

[Forms]![Myform]![Mycontrol]

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

[Forms]![Myform]![Mysubformcontrol].[Form]![ControlOnSubform]

Valid data type entries are shown in Table-3.

Table-3 SQL Parameter Data Types

SQL Parameter Data Types	Equivalent Access Data Type
Char, Text(n)¹, VarChar	Text
Text¹, LongText, LongChar, Memo	Memo
TinyInt, Byte, Integer1	Number, Byte
SmallInt, Short, Integer2	Number, Integer
Integer, Long, Integer4	Number, Long Integer
Real, Single, Float4, IEEESingle	Number, Single
Float, Double, Float8, IEEEDouble	Number, Double
Decimal, Numeric	Number, Decimal
UniqueIdentifier, GUID	Number, Replication ID
DateTime, Date, Time	Date/Time
Money, Currency	Currency
Bit, Boolean, Logical, YesNo	Yes/No
Image, LongBinary, OLEObject	OLE Object
Text, LongText, LongChar, Memo	Hyperlink²
Binary, VarBinary	Binary³

¹ 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.

Example

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");