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

Return All Records When a Query Parameter Is Blank

Is it possible to create a parameter query, where if the parameter input box is left blank will return all of the records?

This article details how, if you design your query using the following criteria, you will be able to query by either a specific value, or leave the parameter box empty to return all of the records:

In the following example, we would like to add criteria to allow us to query for a Salesperson by region, but may also like to view all records as well.

In this case we have a Saleperson field and a Region field.

The parameter query in design view

To the region field, in the OR criteria row you will see the expression [Enter A Region Or Leave Blank To Display All]

In the AND condition you will see the criteria of Is Not Null

You will also note that this same expression also appears as a new field in the next column.

The criteria for this field is Is Null

When you run this query, you will be presented with the following Input box:

The Input box when the query is executed

If a value is entered into this input box, the query will return only the results that contain this value:

The results of running the query with East Midlands as the parameter value

If the Input box is left empty (Is Null), the query will return all records from the table.

The results of running the query without a parameter value being entered

Hopefully this should not cause any problems, and is quite a simple procedure to produce and will be very useful when either some or all records need to be returned.