Browse All Articles > Access Forms - Triple State Checkboxes
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]()
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'.
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:
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.
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.
Comments (6)
Author
Commented:Commented:
Commented:
Author
Commented: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.
Author
Commented:View More