Access Forms - Triple State Checkboxes

Published on
20,029 Points
6 Endorsements
Last Modified:
Level -- Beginner

Check boxes on Access forms are used to indicate discrete true/false information such as "is Employee", "is active", "Send Newsletter", etc.  As such, they are bound to Yes/No (boolean) fields in the underlying tables, which are displayed as True/False, Yes/No, Checked/Unchecked or -1 (true)/ 0 (false).

Occasionally however, users are interested in a third state in addition to true/false values, representing non-commited/don't know/don't care values.  For example, in collecting voting information, in addtion to Yes or No votes, the fact that a participant has not voted yet may be important.  In such a case, preserving that state as Null is important. Triple-state check boxes are designed for this type of data entry.

Access checkboxes by default are two-state (true/false).  However, there is a triple-state property located under the Data Tab in the checkbox's property sheet (see Figure 1).   With the triple-state set to YES, a checkbox can accommodate true/false and NULL data. True appears checked, False appears clear and NULL appears 'grey'.
Figure 1: Triple state property setting
Piece of cake, right?  

There is one small catch, however.  The Yes/No datatype which we typically bind Checkboxes to in Access does not allow NULLs.  It is a special case of a numeric data type which only allows 0s and -1's.   In Access 2003, you can set the 'Required' property of your Yes/No field to NO - but your triple-state checkbox will still only toggle between true and false, since the underlying field cannot hold NULLs. In Access 2010, a triple state checkbox bound to a Yes/No field will simply appear locked.

The trick to making your triple-state checkbox work is to bind it to a numeric/integer field - which allows nulls - and ensure the Required property of that field is set to NO (default).   Figure 2 shows the needed Field Properties:
Figure 2: Field property settings

The stored values in the underlying integer field will be -1, 0 and NULL, and the checkbox on your form will display Checked, Clear and Grey - as desired.

Figure 3 shows the difference between data typical of a standard checkbox and data from a triple-state checkbox.
Figure 3: Sample output from Two-state and Triple-State checkboxes
Enjoy this complimentary article view.

Get unlimited access to our entire library of technical procedures, guides, and tutorials written by certified industry professionals.

Get 7 days free
Click here to view the full article

Using this article for work? Experts Exchange can benefit your whole team.

Learn More
Experts Exchange is a tech solutions provider where users receive personalized tech help from vetted certified professionals. These industry professionals also write and publish relevant articles on our site.
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Learn from the best.