Access 2016: Create a Query with User Input

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.

  1. Screenshot of copying the existing query

    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. Screenshot of pasting the existing query

    Paste the Query

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

  3. Screenshot of naming the Query

    Name the new Query

    Name the query at the prompt and click OK.

    4. Modify the Query

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

    1. Screenshot of right-clicking on the Query in the navigation pane

      Open the Query in Design View

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

    2. Screenshot of the Query in Design View Closeup of the Query in Design View

      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. Screenshot of the user prompt

      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. Screenshot of the Query results

      The Query Results

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