databasedev.co.uk - database solutions and downloads for microsoft access

Plan for Null Responses in Your Parameter Queries

Parameter queries in your database are a favourite with many users because they're efficient, easy to create, and they offer a dynamic solution when you want to query the same field for different criteria. For the most part, parameter queries work great until you decide not to limit the results of the query. For instance, suppose you want to return all of the records in a table. If you ignore the parameter request by entering nothing in response to the prompt, Microsoft Access doesn't return any records. In this article, we'll show you a simple way to avoid this behaviour.

A simple parameter prompt

To illustrate this technique, you'll need a simple parameter query to work with. You can use an existing query, or you can create one based on the table shown in Figure 1. To create this query, select the table you want to base your query on - in our case that's tblSalespersonContact - in the Database window.

Figure 1: We'll create a simple parameter query based on this table.

Next, select Query from the New Object button's dropdown menu. When Access displays the New Query dialog box, double-click on Design View. Add all the fields to the query design grid, and then enter the parameter expression:

[Enter a Region]

in the strRegion field's Criteria cell, as shown in Figure 2.

Figure 2: This parameter query will prompt you for region criteria.

When you run the query by clicking the Run button on the Query Design toolbar, Access will prompt you to enter a region. If you enter East Midlands, as shown in Figure 3, Access will display only those records that contain that city in the strCity field, as shown in Figure 4.

Figure 3: Entering a region in response to this prompt will limit the query's results to only those records containing that entry in the strRegion field.

Figure 4: Our parameter query displays all the matching records for the parameter East Midlands.

If there aren't any matching records, the query will return no records. For instance, if you enter the name East Mids instead of East Midlands, the resulting recordset will be empty.

Ignoring the prompt

As we mentioned at the beginning of this article, you can ignore a parameter prompt. To do so, rerun the query, but don't enter anything when you're prompted - simply click OK. When you do, the query returns an empty recordset.

This may or may not be what you want - most likely it isn't. After all, if you intended to return an empty recordset, why would you bother running the query at all? More likely, you intended to apply no limiting criteria to the query and return all of the records. To configure your query so it ignores the Null parameter and returns all the records, modify the parameter prompt as shown in Figure 5.

Figure 5: The Or expression forces the query to return all the records when you ignore the parameter prompt.

Simply add the Or expression:

Like [Enter a Region] Is Null

Our original expression handles all of the parameter entries as it normally would. The Or expression handles an empty - or Null - parameter.

Once you've modified the query design grid, run the query, and again enter East Midlands in response to the prompt. The query returns the same records, as before, so you know that the additional expression doesn't interfere with the original purpose of your query.

Now, return to Design view and run the query one more time. This time, don't enter anything in response to the prompt - just click OK. Instead of returning an empty recordset, the query returns all the records.

Conclusion

When running a parameter query in your database, you might expect the query to return all the records when you ignore the prompt.

However, that's not what happens - Microsoft Access returns an empty recordset instead. Fortunately, the addition of a simple Or expression lets you work around this behavior.