You can create a query that relies on user input. The value that the user provides determines the result set.

In Access, queries can accept parameters that can be used to determine the results of the query. When the query is run, the user is first prompted to enter a parameter (or multiple parameters). Once the parameter has been supplied, the query can return the results, based on the parameter provided.

For example, we previously created a query that returns all albums from Iron Maiden. This query could be modified so that it returns all albums from a given artist. The artist can be specified by the user.

Access makes this extremely easy to do. In fact, all you need to do is replace "Iron Maiden" with some text enclosed in square brackets.

So let's take a copy of the above query, and modify it so that the user gets prompted to enter an artist name when running the query.

Make a Copy of the Existing Query

Our new query is going to be almost the same as the previous one. So we can save some time and start with a copy of the existing one.

Modify the Query

Now we can modify the copied query to prompt the user for a parameter.

1. Copy the Existing Query

   In the left navigation pane, right-click on the Iron Maiden Albums query and select Copy from the contextual menu.

2. Paste the Query

   Now right-click again inside the navigation pane and select Paste.

3. Name the new Query

   Name the query at the prompt and click OK.

   1. Open the Query in Design View

      In the left navigation pane, right-click the query and select Design View.

   2. The Query Design

      Replace Iron Maiden with [Which Artist?].

      The square brackets will cause a parameter dialog box to open when the user runs the query. The text inside the square brackets will be displayed to the user, and an input field will be displayed for their input.

   3. Run the Query

      Now run the query (either by clicking Run or View in the Ribbon, or clicking the Datasheet View icon in the bottom right corner of the screen).

      A prompt will display, asking for an artist. Enter an artist.

   4. The Query Results

      And if the artist you provide has albums in the database, those albums will be displayed.