Microsoft

Accommodate Null values in Access queries

Not getting the results you want can be frustrating. But if the problem is Null values, it's easily resolved. Use a simple expression to include or exclude Null.

Access' flexible query design grid lets you specify multiple criteria. For instance, you can return clients who live in specific but different regions or states with just one query, instead of writing a separate query for each. There's one gotcha that you need to watch out for though. When you specify criteria, you eliminate Null values. For instance, the following expression returns all the records for Seattle and Portland: "Seattle" Or "Portland"

If that's what you want, fine. On the other hand, if you also want all Null values, you must tell Access to also return Null values. In this case, what you really want is a query that returns all the records that match the specified criteria and any Null values. To do so, add an Is Null component as follows:

"Seattle" Or "Portland" Or Is Null

When searching for specific values, you probably won't want to include Null values, but if you do, be prepared to explicitly specify them.

To eliminate Null values, use the following expression:

Not Is Null

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox