Plan for Null Responses in Your Parameter Queries

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Aa140073.ima-logo(en-us,office.10).gifACCESS 95, 97, 2000

Plan for Null Responses in Your Parameter Queries

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.by Susan Sales Harkins

Parameter queries are a favorite 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, Access doesn't return any records. In this article, we'll show you a simple way to avoid this behavior.

A simple parameter prompt

To illustrate our 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 A. To create this query, select the table you want to base your query on--in our case that's tblSurvey--in the Database window.

Figure A: We'll create a simple parameter query based on this table.
[ Figure A ]

Next, select Query (New Query in Access 95) 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 city]

in the strCity field's Criteria cell, as shown in Figure B.

Figure B: This parameter query will prompt you for city criteria.
[ Figure B ]

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

Figure C: Entering a city in response to this prompt will limit the query's results to only those records containing that entry in the strCity field.
[ Figure C ]

Figure D: Our parameter query displays all the matching records for the parameter Rochester.
[ Figure D ]

If there aren't any matching records, the query will return no records. For instance, if you enter the name Rodchester instead of Rochester, 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 E.

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

Simply add the Or expression

  
Like [Enter a city] 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 Rochester 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, you might expect the query to return all the records when you ignore the prompt. However, that's not what happens--Access returns an empty recordset instead. Fortunately, the addition of a simple Or expression lets you work around this behavior.
 

Copyright © 2000 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.