Access Forms - Triple State Checkboxes

mbizzzzzzup
CERTIFIED EXPERT
Published:
Updated:
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
6
14,917 Views
mbizzzzzzup
CERTIFIED EXPERT

Comments (6)

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Author

Commented:
Thanks for the feedback!  I'm glad you found it helpful.
The data in your example is funky. The check should be showing next to the -1, but it is showing next to the NULL, and the grey should be showing next to the NULL and it is showing next to the -1.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Author

Commented:
Kim,

The columns in the last figure simply illustrate table data generated from a two state checkbox (which saves binary data with no nulls), versus data generated from a separate three state check box (integer with Null, -1, 0, 1).  Despite the column headings being the same (confusing, in retrospect), the controls and columns are independent.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Author

Commented:
I could have sworn the sample database from which those screenshots were taken was originally attached to this article.  There was a bug in EE articles many moons ago which caused file uploads to go missing.  I wonder if that happened here?

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community