parameter query for Access (Parameter query) (ii)

This article focuses on the use of parameter query techniques.

1. Use parameters in multiple fields

We can use parameters in more than one field to generate parameter queries. For example, we have an order form, orders, which have CustomerID and EmployeeID fields, and we want to follow these two fields to find the order that an employee has signed with a customer. We enter [Enter Customer ID] in the criteria line of the CustomerID field in Design view and enter [Enter EmployeeID] in the criteria line of the EmployeeID segment. When you run this query, Access pops up a dialog box that prompts you for "Enter CustomerID", and when we enter CustomerID, we jump out of a dialog box, prompting the message "Enter EmployeeID". When you switch to SQL view, you can see that the SQL statement is written like this:

SELECT orders.*
From Orders
WHERE (Customerid=[enter CustomerID]) and (Employeeid=[enter EmployeeID]);

The order in which the dialog box jumps out is in the order of the argument variable declaration. Here the parameter variable we are implicitly declared, so press from left to right, the variable appears in order to jump out of the dialog box. Let's change the example to--

Parameters [Enter EmployeeID] number, [Enter CustomerID] number;
SELECT orders.*
From Orders
WHERE (Customerid=[enter CustomerID]) and (Employeeid=[enter EmployeeID]);

The first jump is the Enter EmployeeID dialog box, then the Enter CustomerID dialog box.

If you are unfamiliar with SQL statements, Access also provides a tool that allows you to control the order in which the input dialog box appears. Open the query under Design view and click the query menu->Parametersto open the "Query Parameters" dialog box. Here, enter the variable name of the parameter and select a data type for it, and the operation here is equivalent to the parameters declaration in SQL. The order in which the arguments appear from top to bottom is the order in which the dialog box appears.

Note: The variable name entered in the Query Parameters dialog box is consistent with the input in Design view, such as [Enter EmployeeID] in Design view, and here also [Enter EmployeeID]. If not, it means declaring another variable, just not in the query, and this extra argument jumps out of the dialog box.

2. Use multiple parameters in one field

The most common example of this is taking time as the guideline for finding all the records in the start and end time periods. For example, the Orders table has an [Order Date] field, and we want to find the contract that was signed within a certain time period, and we entered Between [Enter Start Date] and [Enter] in the Order Date field in Design view. End Date].

For queries date/time type fields, we generally specify the criteria to be aware of by using # to enclose date values, such as #2008 -3-5#. But in the Argument Query dialog box, we enter the date without adding #, only need to enter 2008-3-5.

3. Use parameters in delete query (delete queries)

All of the examples above are the application of parameters in select query, and the parameter queries for Access can also be used in other queries.

For example, when employees leave, we want to remove the corresponding employee records from the employee table, we can also use parameter query to make the deletion of the work template. We enter the following SQL statement in SQL view--

Delete * FROM employee where employee_name = [Enter employee name]

Each time you run this delete query, only the employee records that meet the input values are deleted.

4. Use parameters in Update query

In the same vein, we can use parameters in an update query. For example, when an employee is transferred from one city to another, we need to update the city fields of the employees who meet the query criteria. Parameters are more useful in this example, not only as criteria for filtering, but also for dynamically giving new city names.

We enter the following SQL statement in SQL view--

PARAMETERS [Enter Employee name] text (255), [New City Name] text (255);
UPDATE Employee SET employee.city = [New City Name]
WHERE employee.employee_name= [Enter Employee name];

Add parameters declaration to let the [Enter Employee Name] dialog box appear first, this is more consistent with the operation of the idea, first find the person to update, and then set the city to a new value. If you do not explicitly declare that the SET clause of the Update statement appears in front of the WHERE clause, although it does not affect the results of the operation, the parameter is only a value substitution, but it does not conform to the custom.

In this example, we extend the function of the parameter, not just as a query criterion.

5. Using parameters in a fuzzy query

Sometimes, we want to use the LIKE operator to do some fuzzy query, use the wildcard * and?. We can leave this task to the user who uses the query, or we can take the job on our own. such as the following wording,

Select * FROM employee where employee_name like [Enter Employee name]

If the user enters "Li *" in the dialog box, he can list all the people surnamed Li. Let the user control the conditions themselves, the resulting recordset more accurate range. If you don't want users to care about wildcard characters, you can put wildcards in the design of the query--

Select * from Employee where employee_name like "*" +[enter employee name]+ "*"

When users use as long as the input "Lee" on it, but will not be surnamed Li, but the name of the person who brought Lee listed.