When the Microsoft team designed version 1, they made two fundamental mistakes with Yes/No fields:
- They decided Yes/No fields could be Yes or No only -- never Null.
- They wrote JET assuming that a Yes/No field would never contain a Null.
(A) violates relational theory, but the real problem is with (B). As any first year computer student knows, all fields on the outer side of join can be Null. So, even basic queries regularly do return Null in Yes/No fields! And since JET was never designed handle this data type with Null, when you operate on this column (apply criteria, group, sort, join, ...) Access crashes completely or generates a nonsense error. For a demonstration, see Outer Join Queries Fail on Yes/No fields.
18 years later, these fundamental design flaws have never been addressed. Clearly, we cannot expect Microsoft to address this problem any time soon.
If you know enough to create an outer-join query, this issue affects you. Most users just know Access crashes, without knowing why.
Use Number fields instead
Once you understand the cause, the workaround is obvious. JET is quite happy working with Nulls in Number fields. Since Access uses 0 for False, and -1 for True, use a Number field in place of the Yes/No field, and JET will process these queries correctly.
For years, developers have been using this as a workaround for (A) above. It is such a simple and obvious solution for (B) as well. Personally, I plan to never use Yes/No fields again. Even where I don't need Nulls stored in the table, I will use Number fields instead of Yes/No so as to avoid the bugs in JET.
Can I still use check boxes?
Yes, you can, though I suggest you reserve the check box for fields where you will not store nulls.
In the lower pane of table design, the field has a property named Display Control on the Lookup tab. The interface does not offer Check Box as an option, but you can set the property programmatically like this:
CurrentDb.TableDefs("Table1").Fields("Field1").Properties("DisplayControl") = CInt(acCheckBox)
substituting your table and field names. If you receive an error saying the property does not exist, set it with SetPropertyDAO(). The field now displays as a check box in datasheets, and Access gives you a check box when you create a form. Set the other properties for this field as illustrated (below left). Note that in table design , the Display control shows as 106 - the value of acCheckBox.
If you do need to store Nulls in the field (solving A as well as B above), a check box will not be a good interface. Although the check box control has a Triple State property, users may find this confusing. Worse, Windows XP and Vista do not display the third state correctly. You cannot fully solve this issue by turning off the Display Themed Controls option in Access, as it still does not display correctly for datasheets. A better interface would be a combo box: the option to leave it blank is more obvious to the user, and the null state is more recognizable. Set the properties as shown at right:
|Properties for replacement Yes/No field (Required)||Properties for Yes/No/Null field|
In practice, you only need to set these fields up once. Import this table into your database. Copy the desired field in table design, and paste it into the table where you need this yes/no field. In fact, you can copy the table above with the two fields set up from here (Access 2000 format, 8KB, zipped.)
Is it worth the effort?
At the time of writing, I have just completed a database with 37 of these quazi-yes/no fields spread through some 70 odd tables. In the end, there were only 3 or 4 fields where it really mattered, i.e. where the queries and subqueries were joined/grouped/filtered in such a way that it would have crashed Access. Typically these queries are created late in the project when everything is being pulled together. For the first time in years, I was able to complete a project without these queries crashing or needing convoluted and inefficient fudges (such as Nz() or IIf() expressions around the yes/no fields where they appear on the outer side of joins.)
The effort to create these fields was minimal (copy 'n' paste.) The relief at seeing them work flawlessly was almost palpable. The choice is a no-brainer.
There is one additional consideration. When you add one of these fields to a table, existing records are populated with Null. You must therefore run an Update query to set the existing rows to False if that is the result you want.
This is such a simple, effective solution, I plan to never use Yes/No fields in JET again.