| The previous section showed how you can use criteria in Microsoft Query to resolve problems caused by null values in pivot tables. Microsoft Query provides more flexible criteria, termed parameters, in much the same way as does Microsoft Access. When you tell Microsoft Query, or a database manager such as Microsoft Access or SQL Server, that you want it to return only certain records, you do so by means of a criterion. Perhaps you want to see only those birth records where the birth weight exceeds 2,000 grams. Then you would supply that weight as a criterion (see Figure 6.20). Figure 6.20. You can supply more than one Criteria field. They will act as though they were connected by Ands. 
As shown in Figure 6.20, the person creating the query has entered the value >2000 in the Criteria pane. When he chooses File, Return Data to Microsoft Excel, only those records with a birth weight of more than 2,000 grams are returned. If the user has based an external data range or a pivot table on this query, each time the data range or the table is refreshed it will get only those records with a value greater than 2,000 on the birth weight field. But if the user puts a special sort of criterion, a parameter, in the Criteria pane, he can change the value of the criterion each time the query runs. A parameterized query is shown in Figure 6.21. Figure 6.21. Notice the use of the comparison operator > before the parameter. Operators are frequently useful, but are not required in parameterized queries. 
By enclosing the criterion in square brackets, the user has established it as a parameter. Both Microsoft Query and Microsoft Access recognize the square brackets as signaling a parameter. When the query is executed, the user is prompted to supply a value that will be used as a criterion. Executing the query shown in Figure 6.21 first displays the dialog box shown in Figure 6.22. Figure 6.22. The dialog box prompting for a value appears if you refresh a worksheet data range that gets its data from this query. 
Why is this useful? Because each time the query executes, it prompts the user for the value to use as a criterion. That means that the user can supply a different value each time. In this example, the user could specify 2000 as a criterion one time and the query would return only those records with a birth weight greater than 2,000; the next time, the user could specify 2500 and the query would return only those records with a birth weight greater than 2,500. You can arrange to supply a range of criteria by using the keyword Between. Suppose that you wanted to return only those records with a value in birth weight greater than or equal to one value and less than or equal to another value. You can use the parameter Between [Lower Weight] And [Higher Weight]. When the query runs, it will prompt the user first for a value for Lower Weight and then for a value for Higher Weight. After the second of the two criteria has been supplied, the query will return only those records that satisfy the two criteria. The Between...And... usage also works well with date and time data for example, Between [Starting Date] And [Ending Date].  You'll find information on using parameters with queries by means of VBA, in "Bringing Data Back from a Parameterized Query," p. 293.
| 