How To Use Access Queries With Null Values

Access Query Designer

The Access database query grid designer is an excellent tool for locating the records you are searching form within your database. The Query designer gives you plenty of opportunity to build multiple criteria into your query. You can use the Access query designer to search for individuals who are located in multiple states, zip codes, countries and a variety of combinations. This eliminates the need for building many queries then attempting to connect them, complicating your code and slowing performance.

There is one caveat regarding queries. The Access query designer can result in mixed results when there are "Null" or empty values in the data table you are searching. As an example, you are searching for all contacts in Portland or Seattle. Your Access database will promptly return those values. Here's what the query looks like in the query designer:

Running the query returns the following:

Using Access Query Designer To Find Nulls

We now know that 4 records meet the criteria for contacts in Portland and Seattle, but what about those records that may be missing an entry in the City field? To bring back records with null values, change the criteria to request specifically those records:

Here is the result for the query, returning a record with no entry in the City field. Notice that the query does not return those records with City equal to Portland or Seattle.

Use An Access Query To Locate All Records

You can add more criteria to the query to view all records:

The following shows the records returned:

The query returns all records with contacts located in Portland, Seattle, plus those records with no City entry.