Flaws in Microsoft Access

Provided by Allen Browne, July 2006. Updated June 2007

Outer join queries fail on Yes/No fields

Yes/No fields in Access tables cannot be Null. When necessary, you can work around this limitation it by using a Number field instead, permitting the values -1 (for true), 0 (for false), and Null.

But Yes/No fields do contain Nulls in queries that use outer joins. JET (the query engine in Access) cannot handle these. Simple queries fail with an error such as, "No current record." More complex queries (particularly those with subqueries where this occurs) can crash Access.

Simple example

The sample database (10KB zipped) contains just two tables: companies, and their employees. IsInvited is a Yes/No field, so choose the people to include in a mailing. The query counts the number of people from each company who are invited or not invited:

Design View	SQL View
	SELECT tblCompany.CompanyID, tblEmployee.IsInvited, Count(tblEmployee.EmployeeID) AS CountOfEmployeeID FROM tblCompany LEFT JOIN tblEmployee ON tblCompany.CompanyID = tblEmployee.CompanyID GROUP BY tblCompany.CompanyID, tblEmployee.IsInvited;

Access cannot run this query. The only result is a dialog reading "No current record."

The error occurs because we asked Access to group by a Yes/No field that contains nulls. To verify that this is the cause, choose First in the Total row under IsInvited. This removes the Yes/No field from the GROUP BY clause, and the query runs (though it does not achieve what we needed.)

Clearly, there is a basic flaw in the way JET processes Yes/No fields that contains nulls. The bug is present in all versions of Access.

May 2007 update

Workaround

As a workaround for this example, use Int() around the Yes/No field, like this:

SELECT tblCompany.CompanyID,
    Int([IsInvited]) AS WhetherInvited,
    Count(tblEmployee.EmployeeID) AS CountOfEmployeeID
FROM tblCompany LEFT JOIN tblEmployee
    ON tblCompany.CompanyID = tblEmployee.CompanyID
GROUP BY tblCompany.CompanyID, Int([IsInvited]);

Using Int() around the yes/no field has the effect of converting it into a Number field. JET handles Null in numeric fields, so the bug with the Yes/No type it averted.

Unfortunately, the workaround slows the query down since JET cannot use any index on the Yes/No field. Even with moderately sized tables (tens of thousands of records), it may be an order of magnitude slower.

Alternative

An alternative that does not have the performance penalty is to use a Number field in your table instead of a Yes/No field. Assuming that you do not want Nulls stored in the fields (i.e. they arise only in outer join queries), you can set the Field properties as shown for the FakeYN field shown at right.

The field is a Number type, with Field Size set to Integer. Access uses 0 for False and -1 for True, so we set the Validation Rule so the field accepts only these two values. We set the Default Value to zero, so it defaults to False like a yes/no field. We set the Required property to Yes, so nulls are not stored in the field.

Now we need to get Access to display the field as a check box. The Lookup tab lets you set the Display Control property, but Access does not offer Check Box as an option. We therefore have to set the property programmatically. If the property has never been set, it must be created. If it has been used, we just need to set it. This custom function handles both cases: SetPropertyDAO()

After copying the function into your database, open the Immediate Window (Ctrl+G) and use it like this:

Call SetPropertyDAO(CurrentDb.TableDefs("tblEmployee").Fields("FakeYN"), "DisplayControl", dbInteger, CInt(acCheckbox))

The table will now display the field as a check box, and it will behave as a yes/no field, without the bug in outer-join queries.

Notes:

1. If you add data to this table programmatically, you may need to explicitly assign a zero to the field in some contexts.
2. If you do want a triple-state yes/no/null field, interface it with a combo instead of a check box. You can set the Triple State property of a check box on a form, but this does not work for the table itself or queries. Also Windows XP and Vista do not visually distinguish the 3 states of the triple-state check box unless you turn off the Use Windows Themed Controls on Forms option. Even then, they still don't show the 3rd state correctly in a Datatsheet form, unless you revert to the Windows Classic theme.
3. To use a combo box as the display control instead of a check box, set these properties for the field on the Lookup tab in table design:

Follow up: June 2007

A new article - Why I stopped using Yes/No fields - describes this technique in more detail.