Access Forms - Triple State Checkboxes

Published on
18,580 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
  • 2

Expert Comment

thanks mbizup! i was about to post a question on this when i came across your article. really helpful! = )
LVL 61

Author Comment

Thanks for the feedback!  I'm glad you found it helpful.

Expert Comment

= )

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Join & Write a Comment

How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Wrapper-1-Query. Use an Excel function to calculate a column for an Access query. Part 1. Shows a query in Access that has a calculated column with the results of an Excel worksheet function. See how to call a wrapper function from a query, and …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month