MS-Access Tips for Casual Users

Provided by Allen Browne, allenbrowne.com

The Query Lost My Records!

Nulls in Criteria

You wish to mail two different messages - one to your WA friends and another to those in other states. You create two queries: one where the Criteria line under State reads WA , and a second query where the Criteria line reads Not "WA" . You could think this would take care of all cases. It doesn't!

The problem is the way Access and other true relational databases handle Nulls. If the entry under State has been left completely blank, the record will not show up in either of the above queries. You must specifically ask Access to check for Nulls, by using a Criteria such as Is Null Or Not "WA". Whenever you enter criteria for a query, think about the possibilities of Nulls.

In some cases, you will want to prevent Nulls occurring. In Access 2 or later, open the table in Design View, click on the field, and in the properties at the bottom of the screen, set Required to "Yes".

Nulls in Joins

The same "Gotcha!" occurs when you have two tables joined on a field that can contain a Null. For example, a Customer table might be joined to an Invoice table on a CustomerID field. To cover "Cash Sales", you allow invoices to be entered with a blank CustomerID.

Now you create a query with both the Customer and Invoice tables, as the basis for a report showing all sales. If you are observant, you notice that your Cash Sales are missing, so the report has incorrect totals!

The solution is very simple. In the Query Design grid, double-click the line that joins the two queries, and Access will pop up a dialog box asking what type of JOIN you want. Select the appropriate OUTER JOIN, and all the "Cash Sales" will reappear in your query and in the report.

For more information about handling nulls, see: Common errors with Null.