Report Parameters

Pass data to SQL statements at run time

Use Report Parameters to pass information to the report's SQL string at run time.  A Parameter dialog will prompt the user for input when generating reports.

Report Parameters Dialog

The syntax for a report parameter is:



  • ParameterName:  Must be a unique Parameter name and is often the same or similar to the field name the parameter relates to.
  • PromptString:  The text displayed in the Parameter dialog asking for input.
  • DefaultValue:  The default value of the Parameter.
  • Type:  The Parameter type code of the parameter.

Parameter Type Codes

  • AS:  String
  • AD:  Date
  • AB:  Boolean
  • AI:  Integer
  • ADB:  Double
  • AC:  Combobox (i.e., drop down list)

The following is an SQL statement that uses a 'hard coded' date, which means the SQL statement must be edited each time the default date needs to change.

FROM   qryEquipmentMaster
WHERE  CalibrationDue < #10/15/2012# 

Conversely, the following sample shows the same SQL statement using a Parameter, which allows users to enter a date value at run time.

FROM   qryEquipmentMaster
WHERE  CalibrationDue < #<%CalibrationDue|Due Date:||AD%># 

Date Default Values

When using the AD (Date) Parameter Type code, several options are available for the Default Value:

  • Specific Date:  Exact date with the syntax of #MM/DD/YYYY#
  • Number of Days:  Positive or negative integer (counting number) indicating the number days added (or subtracted) from the current date
  • BOM:  Beginning of current month
  • EOM:  End of current month
  • BONM:  Beginning of next month
  • EONM:  End of next month
  • BOPM:  Beginning of previous month
  • EOPM:  End of previous month

The following example uses the EOM Default Value code for the End of the Current Month:

FROM   qryEquipmentMaster
WHERE  CalibrationDue < #<%CalibrationDue|Due Date:|EOM|AD%># 

Combobox Parameter Type

Using the AC (Combobox) data type requires the use of the DefaultValue Parameter field to configure the Combobox.  Within DefaultValue exactly six sub-fields, each separated by a slash ("/"), are required.  This also means that exactly 5 slashes must be present in the DefaultValue field.

The six sub-fields are:

  1. Table or SQL *:  Table or SQL for the records used in the combobox
  2. Value Member *:  Field name for the value, usually an ID field
  3. Display Member *:  Field name for the text displayed in the combobox
  4. Filter:  SQL filter phrase that limits the records displayed (e.g., "Active = -1")
  5. Sort:  SQL sort phrase that places the list of choices in a specific order (e.g., "Name").  When left blank, the Display Member is the default sort field.
  6. Default Value:  One of the values within the list created by the Value Members field (e.g., "55ec4215-7f9b-4e9f-b583-56be9871b895")

Although not all fields are REQUIRED (*), their places must be defined with slashes like in the following DefaultValue example.  Note that there are still five slashes ("/") even when only three sub-fields are used.


Last updated:  11 Feb 2014