The query lost my records!

        1 vote: **        2,662 views      0 comments
by Allen Browne, 20 April 2005    (All versions of Access)

MS-Access Tips for Casual Users

Provided by Allen Browne, allen@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.


Home Index of tips Top

Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'The query lost my records!'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code


It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.