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: Must be a unique Parameter name not used for any other parameter or field in the report.
- PromptString: The text displayed in the Parameter dialog asking for input (e.g., Calibration Due By:)
- DefaultValue: 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 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.
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.
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:
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.
Last Updated: 12 February 2018