A parameter query is a query that displays its own dialog box prompting the user for information, such as criteria for retrieving records or a value for a report field. You can design the query to prompt the user for more than one piece of information; for example, you can design it to retrieve two dates. C1Report then retrieves all records that fall between those two dates.
You can also create a monthly earnings report based on a parameter query. When you render the report, C1Report displays a dialog box asking for the month that you want the report to cover. You enter a month and C1Report prints the appropriate report.
To create a parameter query, you need to edit the SQL statement in the RecordSource property and add a PARAMETERS clause to it. The syntax used to create parameter queries is the same as that used by Microsoft Access.
1. The easiest way to create a parameter query is to start with a plain SQL statement with one or more items in the WHERE clause, then manually replace the fixed values in the clause with parameters. For example, starting with the plain SQL statement:
strSQL = "SELECT DISTINCTROW * FROM Employees " & _
"INNER JOIN (Orders INNER JOIN [Order Subtotals] " & _
"ON Orders.OrderID = [Order Subtotals].OrderID) " & _
"ON Employees.EmployeeID = Orders.EmployeeID " & _
"WHERE (((Orders.ShippedDate) " & _
"Between #1/1/1994# And #1/1/2001#));"
2. The next step is to identify the parts of the SQL statement that will be turned into parameters. In this example, the parameters are the dates in the WHERE clause, shown above in boldface. Let's call these parameters Beginning Date and Ending Date. Since these names contain spaces, they need to be enclosed in square brackets:
strSQL = "SELECT DISTINCTROW * FROM Employees " & _
"INNER JOIN (Orders INNER JOIN [Order Subtotals] " & _
"ON Orders.OrderID = [Order Subtotals].OrderID) " & _
"ON Employees.EmployeeID = Orders.EmployeeID " & _
"WHERE (((Orders.ShippedDate) " & _
"Between [Beginning Date] And [Ending Date]));"
3. Finally, the parameters must be identified in the beginning of the SQL statement with a PARAMETERS clause, that includes the parameter name, type, and default value:
strSQL = "PARAMETERS [Beginning Date] DateTime 1/1/1994, " & _
"[Ending Date] DateTime 1/1/2001;" & _
"SELECT DISTINCTROW * FROM Employees " & _
"INNER JOIN (Orders INNER JOIN [Order Subtotals] " & _
"ON Orders.OrderID = [Order Subtotals].OrderID) " & _
"ON Employees.EmployeeID = Orders.EmployeeID " & _
"WHERE (((Orders.ShippedDate) " & _
"Between [Beginning Date] And [Ending Date]));"
When this statement is executed, the control shows a dialog box prompting the user for Beginning Date and Ending Date values. The values supplied by the user are plugged into the SQL statement and the report is generated only for the period selected by the user.
The dialog box is created on-the-fly by C1Report. It includes all parameters in the query, and uses controls appropriate to the parameter type. For example, check boxes are used for Boolean parameters, and date-time picker controls are used for Date parameters. Here is what the dialog box looks like for the SQL statement listed above:
The syntax for the PARAMETERS clause consists of a comma-separated list of items, ending with a semi-colon. Each item describes one parameter and includes the following information:
• Parameter name. If the name contains spaces, it must be enclosed in square brackets (for example, [Beginning Date]). The parameter name appears in the dialog box used to collect the information from the user, and it also appears in the WHERE clause for the SQL statement, where it is replaced with the value entered by the user.
• Parameter type. The following types are recognized by the control:
|
Type Name |
ADO Type |
|
Date |
adDate |
|
DateTime |
adDate |
|
Bit, Byte, Short, Long |
adInteger |
|
Currency |
adCurrency |
|
Single |
adSingle |
|
Double |
adDouble |
|
Text, String |
adBSTR |
|
Boolean, Bool, YesNo |
adBoolean |
• Default value. This is the value initially displayed in the dialog box.
The easiest way to build a parameterized query is incrementally. Start with a simple query that works, then add the PARAMETERS clause (don't forget to end the PARAMETERS clause with a semi-colon). Finally, edit the WHERE clause and add the parameter names at the proper place.
You can use the GetRecordSource method to retrieve a proper SQL statement (without the PARAMETERS clause) from a parameter query. This is useful if you want to create your own recordset using data contained in the report.
Note: Instead of using a parameter query, you could write code in Visual Basic or in C# to create a dialog box, get the information from the user, and fix the SQL statement or set the DataSource object's Filter property as needed. The advantage of using parameter queries is that it places the parameter logic in the report itself, and it is independent of the viewer application. (It also saves you from writing some code.)
|
