Parameter+Query+access

You should use parameters with your query. First let's review this requirement in context. Databases exist for the primary purpose of serving up information: "What's our best-selling product? Who's our best customer? Where are we not meeting our sales targets?" All are questions that you might legitimately ask of a well-designed database. To get the answers from your Access database, you create a select query (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.), choose the appropriate tables, and enter the needed criteria  (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.). Over time, you'll often find yourself asking the same questions, and therefore running many of the same queries repeatedly. Sometimes, however, the question remains the same while the search values, or criteria, change. For instance, you might want to see sales for the Beverages category today, the Condiments category tomorrow, and the Confections category the day after tomorrow (pardon me a moment while I snack on some dark chocolate). This means you must open your query in Design mode, change the criteria to specify Beverages, run the query, change it again to specify Condiments, run the query again, change it again to specify Confections, and so on.
 * Question: How do I create a query that lets me alter a criteria value without having to modify my query design every time?**

If you frequently run the same query but change the criteria each time, you'll save time by using a query parameter. With a query parameter, you can create a query that asks the user to supply the search value. This way, you can use the query over and over without modifying its design.

Introducing query parameters
A query parameter (often called just a parameter) is a placeholder for an actual value. To use a query parameter, you first open an existing query, or if you don't have one, create a new query and choose your tables and fields. To create the parameter, type a phrase enclosed in brackets in the **Criteria** row of your query design grid (see the example shown inside the red circle below). When you run the query, Access sees the bracketed parameter and prompts you to enter a value in the **Enter Parameter Value** dialog box. The value you enter is passed to the query as the parameter. It's as if you typed the value directly into the query design grid — but you didn't have to modify the query. The text you supply within the brackets of the parameter becomes the prompt that you see in the **Enter Parameter Value** dialog box, so you should choose your phrase carefully and make sure it clearly indicates the information that needs to be entered. The phrase also serves as the name that Access uses to identify the parameter. After you enter the value — Confections, for example — Access processes the query, selects the matching data, and presents the results in a datasheet.

If you press ENTER without supplying a value, Access displays an empty datasheet.

Using more than one parameter
A query that contains a parameter is called a //parameter query//. Sometimes you want a parameter query that prompts for more than one value. For instance, you might want to design a query that lets you summarize product sales. Every time you run the query, you want to enter both the sales category and the start and end date that specify the date range for the sales. For such a query, you would need three parameters. Access lets you specify parameters in each of several fields; plus, you can specify more than one parameter in a single field. For this example, you would add a parameter to the **CategoryName** field and two parameters to the **OrderDate** field. In the **OrderDate** field, you would use the built-in operator called **Between...And** in conjunction with the parameters to tell Access to include sales only where the order date falls between the specified start date and end date. The resulting query design grid would then look something like this: When you run the query, Access prompts you with three successive **Enter Parameter Value** dialog boxes: one for the category name, one for the start date, and one for the end date.

Using the Query Parameters dialog box
Often, you'll want to have more control over the order in which the **Enter Parameter Value** dialog boxes appear. For instance, you might want to make sure the **Category Name** prompt appears before the **Enter Start Date** and **Enter End Date** prompts. You can do this by using the **Query Parameters** dialog box. To open the **Query Parameters** dialog box, first open your query in Design view, and then, on the **Query** menu, click **Parameters**.

The values you enter in the **Query Parameters** dialog box control two things: first, the order in which to display the parameters, and second, the data type to expect for each parameter. To populate the dialog box, enter the parameters in the order you want them to appear, being careful to enter the text exactly as it appears in the query design grid. You can also select the text in the grid, press CTRL+C to copy, and press CTRL+V to paste the text into the fields of the **Query Parameters** dialog box. Note that the parameter text is only the text within the brackets; it does not include operators such as **Between** and **And**. When you run your parameter query, Access uses the data types you chose in the **Query Parameters** dialog box to validate the data that is entered. For instance, if you chose the **Date/Time** data type and entered **31-Feb-2004** (a date that could never occur), Access displays an error message that says, "The value you entered isn't valid for this field," and you'll have to re-enter the date. When you enter a parameter in the **Query Parameters** dialog box and don't specify a data type, Access converts the value entered into the **Text** data type.