Parameter Queries

With Microsoft Jet, you can specify one or more query parameters. The parameters are used to accept input values at run time from the user. This user-supplied data is then merged with a QueryDef object. Parameters are commonly used to supply selection criteria, but they can also be used to specify terms in an expression.

You declare parameters with the PARAMETERS declaration, which specifies the names and data types of the parameters. The following example takes an EmployeeID value as a parameter, which causes the query to return values for only the selected employee, and another parameter whose value will be multiplied by the employee’s current salary to calculate a proposed new salary. The first parameter, EmployeeIDSelect, is used as part of the selection criteria; the second parameter, SalaryIncreaseFactor, is used as part of an expression:

PARAMETERS EmployeeIDSelect Long, SalaryIncreaseFactor Single;
SELECT 
	Employees.LastName, 
	Employees.FirstName, 
	Employees.Salary, 
	CCur(Employees.Salary*SalaryIncreaseFactor) AS NewSalary
FROM Employees
WHERE Employees.EmployeeID = EmployeeIDSelect;

How does your program supply the parameter values? If you’re working with Microsoft Access, you may be familiar with the way it prompts the user to supply missing parameter values.

This behavior isn’t built into Microsoft Jet, however. It’s a function performed by Microsoft Access, and then only when the parameter query is executed through the user interface. Microsoft Access prompts the user for parameter values for forms, reports, and datasheets based on parameter queries.

If you want to supply parameter values to an existing QueryDef object that’s being executed programmatically, you have to do so explicitly, as in the following example. Assume that you have saved the parameter query shown previously as a permanent QueryDef object. In this example, strDbPath is the path to the NorthwindTables database, strQueryName is the name of the query under which you have saved the preceding SQL statement, lngEmpID is the Long value you supply for the EmployeeIDSelect parameter, and sngIncFact is the Single value you supply for the SalaryIncreaseFactor parameter.

For example, to increase the salary for the first employee in the Employees table by 10 percent, you would supply a value of 1 for EmployeeIDSelect and a value of 1.1 for SalaryIncreaseFactor:

Dim dbs As Database
Dim qdf As QueryDef
Dim rst As Recordset
Dim strMsg As String

Set dbs = OpenDatabase(strDbPath)
' Return reference to parameter QueryDef object.
Set qdf = dbs.QueryDefs(strQueryName)

' Supply values for parameters.
qdf.Parameters("EmployeeIDSelect") = lngEmpID
qdf.Parameters("SalaryIncreaseFactor") = sngIncFact

' Open recordset.
Set rst = qdf.OpenRecordset()
With rst
	If .RecordCount <> 0 Then
		MsgBox "The proposed new salary is: $" & !NewSalary
		' Check whether recordset is updatable.
		If .Updatable Then
			' Prompt to update salary.
			strMsg = "Would you like to update the salary for " _
				& !FirstName & " " & !LastName & " from $" _
				& !Salary & " to $" & !NewSalary & "?"
			' Update salary field.
			If MsgBox(strMsg, vbYesNo) = vbYes Then
				.Edit
				!Salary = !NewSalary
				.Update
			End If
		End If
	End If
End With

This method assumes that you know the names of the parameters at the time your code is written. One way to avoid the need to explicitly specify parameters is to iterate through all the parameters in the Parameters collection of the QueryDef object and prompt the user for values, thus emulating the behavior inherent in Microsoft Access. In the following example, strDbPath is the path to the database, and strQueryName is the name of the query:

Dim dbs As Database
Dim qdf As QueryDef
Dim rst As Recordset
Dim prm As Parameter

Set dbs = OpenDatabase(strDbPath)
Set qdf = dbs.QueryDefs(strQueryName)

For Each prm In qdf.Parameters
	prm.Value = InputBox("Enter parameter value: " & prm.Name)
Next prm
Set rst = qdf.OpenRecordset()

Although Microsoft Jet includes the PARAMETERS declaration in order for you to name and type your QueryDef object parameters explicitly, it actually treats any unrecognized term as a parameter. If you make a spelling error when specifying a field name, for example, Microsoft Jet treats the misspelled field name as a parameter.

It’s a good idea to get into the habit of specifying the names and data types of your parameters rather than relying on the implicit parameter behavior of Microsoft Jet.

You can also use the Microsoft Jet PARAMETERS declaration to create a temporary QueryDef object that prompts the user to supply parameter values when your code executes. For more information and an example, see “Temporary QueryDef Objects” in Chapter 3, “Data Definition and Integrity.”

Important You should not use a parameter query to search for Null values. A parameter of Null will never find a Null record because no two Null values match. If you want to find records that contain fields with Null values, you can use a non-parameter SELECT query. For example, the following SQL statement returns a list of all customers that don’t have a fax number:

SELECT * FROM Customers WHERE Fax = Null;

See Also For a discussion of the various database object collections, including QueryDefs and Parameters, see Chapter 3, “Data Definition and Integrity.”