Report Parameters
Pass Data to SQL Statements at Run Time
Use Report Parameters in Calibration Control (our Calibration Management Software) to pass information to the report's SQL string at run time. A Parameter dialog will then prompt the user for the required input when generating reports.
The general syntax for a report parameter is:
<%ParameterName|PromptString|DefaultValue|Type%>
Where:
- Parameter Name: Must be a unique Parameter name not used for any other parameter or field in the report.
- Prompt String: The text displayed in the Parameter dialog asking for input (e.g., Calibration Due By:)
- Default Value: The default value of the Parameter.
- Type: The Parameter type code of the parameter (see below).
Parameter Type Codes
- AS: String
- AD: Date
- AB: Boolean
- AI: Integer
- ADB: Double
- AC: Combobox (i.e., drop-down lists)
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.
SELECT *
FROM qryEquipmentMaster
WHERE CalibrationDue < #2018-10-15#
Conversely, the following sample shows the same SQL statement using a Parameter, which allows users to enter a date value at run time.
SELECT *
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 #YYYY-MM-DD# for MS Access or 'YYYY-MM-DD' for MS SQL Server.
- 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:
SELECT *
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:
- Table or SQL *: Table or SQL for the records used in the combobox.
- Value Member *: Field name for the value, usually an ID field.
- Display Member *: Field name for the text displayed in the combobox.
- Filter: SQL filter phrase that limits the records displayed (e.g., "Active = -1") when using a Table name instead of a SQL statement
- 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. This field is only valid when using a Table name instead of a SQL statement.
- Default Value: One of the values within the list created by the Value Members field (e.g., 55ec4215-7f9b-4e9f-b583-56be9871b895 without apostrophes or quotes).
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.
tblPeople/PersonId/DisplayName///
Last Updated: 8 July 2019
