<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Access Forms - Triple State Checkboxes

Published on
19,296 Points
12,696 Views
6 Endorsements
Last Modified:
Approved
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
Comment
Author:mbizup
  • 3
  • 2
6 Comments

Expert Comment

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

Author Comment

by:mbizup
Thanks for the feedback!  I'm glad you found it helpful.
0

Expert Comment

by:developingprogrammer
= )
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Expert Comment

by:Kim Howard
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.
0
LVL 61

Author Comment

by:mbizup
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.
0
LVL 61

Author Comment

by:mbizup
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?
0

Featured Post

Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Join & Write a Comment

Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
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