Triple State
By Richard Rost
4 years ago
How to Use Triple State Check Boxes in Access
In this Microsoft Access tutorial, I will show you how use a triple-state checkbox to save a null value to indicate "I don't know" the answer to a yes/no question.
Stanley from Wilmington, Delaware (a Platinum Member) asks: I have a few yes/no fields in my school database for things like IsParent, IsFaculty, IsStudent, and so on. However, sometimes when we enter the data, we don't know some of these things. For example someone could be a faculty member but we don't know if they're enrolled in classes too (it happens). However with a yes/no field, the database indicates that they're not a student when they are. How do I handle this?
Recommended Course
Links
 Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, What is triple state in access, Triple State Checkboxes, Triple State Yes/No Check Box, yes/no fields, null values
Subscribe to Triple State
Get notifications when this page is updated
Intro In this video, I will show you how to use triple state check boxes in Microsoft Access, explaining what triple state means and how it can help when you need to store Yes, No, or Unknown values in your database. You'll learn how to set up your table fields and forms to allow for this third "I don't know" option, instead of being limited by standard Yes/No fields. I'll walk you through converting existing fields and configuring checkboxes on your forms to cycle between all three states.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I am going to show you how to use triple state check boxes in Microsoft Access, and I'm going to explain exactly what triple state means.
Today's question comes from Stanley in Wilmington, Delaware, one of my Platinum members. Stanley says, "I've got a few Yes/No fields in my school database for things like IsParent, IsFaculty, IsStudent, and so on. However, sometimes when we enter data, we don't know some of these things. For example, someone could be a faculty member, but we don't know if they are enrolled in classes too. It happens. However, with a Yes/No field, the database indicates that they're not a student when they are. How do I handle this?"
Well, Stanley, that's exactly what that third state or triple state is for. You want to know Yes, No, or I don't know, or sometimes it could be I don't care, like if you're doing a survey. So there's Yes, there's No, and then there's Null. Null means I don't know. I don't have that data.
A typical Yes/No field does not allow a Null value. It has to be either Yes or No. Let me show you how we can get around that.
Here I am at my TechHelp Free Template. This is a free database. You can download a copy from my website if you want to. You'll find links down below.
Now, in here I have a customer form with an IsActive field, and this is a traditional Yes or No value. It's either active or they're not. If they're a new customer, they just bought from you, they're active. If they move out of your area, or they die, or whatever, they're now inactive. There really wouldn't be a need for a Null for this.
So let's go to our Customer table and let's go to Design View. Here is IsActive, Yes/No. Let's put your three fields in here: IsParent, and I'm going to make this a Number, type Long Integer. I'm going to set the Default Value down here to blank, which is Null, which means I don't know. Offhand, I don't know if this person is a parent or not. I don't have this information.
You can't store that in a Yes/No field; you have to use a Number, a Long Integer.
Same thing: we have IsStudent. Make that a Number, Default Value of Null, and make sure Required is also set to No. If you set that to Required, then they have to pick a value. They can't leave it Null.
Then we have, what's the other one, IsFaculty. Number, Default Value blank, Required is No. Save that.
Now, when we go into our Customer form, if I go into Design View, and if I add Existing Fields and try to bring these three fields in, which if you don't know how to do this, by the way, watch my Access Beginner 1 class. When I bring these in, they come in as text boxes as Number fields.
We're going to just delete those. All you have to do in this case, you can copy an existing checkbox if you want to, or you can grab a new one off of here. I'll just copy this one since I already have one. Right-click, Copy, Paste, and let's bring this one guy over here.
Open up the properties for it. Go to the All tab. First thing is go to Control Source. We're going to bind this to the field which is IsParent. I'm going to copy that and paste it over that to give you the same name. Control Source is the field in the table that this checkbox saves its data to. That's what it's bound to. We also want to make it have the same name. We don't want it to be called Check14 or whatever.
Right down here, there's a field called Triple State. Set that to Yes. What that allows you to do is you can toggle between three things: Yes, No, and Null, which means I don't know. The default value in the table is going to be Null.
Now we just copy that twice more. Copy, Paste. This one is IsParent; let's change the label to IsParent or just Parent for the label. The label doesn't matter.
This one is going to be IsStudent. Copy, Paste. I'll change the label to Student.
And then this one is going to be Faculty, the last one. Copy, Paste, move it over, Faculty.
So we have Parent, Student, Faculty. Save it, close it, close it, let's open it back up again.
There we go. Now here's me. I don't know if I'm a parent, student, or faculty. If you want to check these on or off: one click to check it on, two clicks for off, three clicks for I don't know. That's how you cycle between these.
You can go through your records and do that.
You've also seen me use these triple state checkboxes before for selecting criteria in my Search Form 2.0 video, some of my TechHelp videos. This is free.
I have my Active field here, which is a standard Yes/No checkbox, but I show you how to use a triple state checkbox up here for the filter, because you can say Yes, No, or I don't care, which means show me all of them, show me whether they're active or not. You can mix and match these.
I'll put a link to this video down below if you want to watch it. It's free. It's on my YouTube channel and on my website.
That's primarily where I use triple state checkboxes – for filtering. The nice thing about this is if you use this method, the value that gets saved in your table is going to be either a zero for No, a negative one for Yes, which is the same value that a Yes/No field will save. If you want to take a current existing field, like this IsActive that I have here, and convert it to a Number, you can get rid of the Default Value. Again, make sure Required is No and it will preserve all of your existing records.
If you save it, come back into your form, you'll see this is still going to work. It will keep all your Yes/No values as they are, but now you can come in here, and you can change that to Triple State: Yes. Now this guy is now triple state, but you have to save that value in a Number field.
That's it. That's your triple state.
If you want to learn more about how to use triple state checkboxes as filters and a whole lot more, I cover all kinds of stuff like this in my Search Seminar. I'll put a link to this down below as well if you want to check it out.
So that's your Fast Tip video for today. I hope you learned something. We'll see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free Expert class each month after you finish the Beginner series.
Platinum members get all the previous perks plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free Developer class each month after you finish the Expert classes. These are the full-length courses found on my website, not just for Access too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page. You'll be shown in each video as long as you are a sponsor. You'll get a shout-out in the video and a link to your website or product in the text below the video and on my website.
But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.Quiz Q1. What is a triple state checkbox primarily used for in Microsoft Access?
A. To allow only Yes or No responses
B. To enable the options Yes, No, or Null (unknown)
C. To provide more color options for checkboxes
D. To improve form layout with multiple columns
Q2. Why would you choose a triple state checkbox instead of a standard Yes/No field?
A. To record only two possible outcomes
B. To reduce database size
C. To handle situations where the answer is unknown or not applicable
D. To prevent users from leaving fields blank
Q3. In Access, what value does Null represent in a triple state checkbox?
A. Yes
B. No
C. I do not know
D. Always False
Q4. When designing a table to use a triple state checkbox, which field data type should you use?
A. Yes/No (Boolean)
B. Short Text
C. Number (Long Integer)
D. Currency
Q5. What must you ensure about the 'Required' property of a field used for triple state checkboxes?
A. Set to Yes, so every field is filled in
B. Set to No, to allow Null values
C. It does not affect the checkbox
D. Must match the label name
Q6. What happens if you use a Yes/No field instead of a Number (Long Integer) for storing triple state values?
A. The database will accept Null values
B. Only Yes or No values can be stored, not Null
C. The field will function as a drop-down menu
D. The form will automatically convert to triple state
Q7. What is the purpose of setting the Triple State property to Yes in a checkbox control?
A. To use images instead of checkboxes
B. To enable three possible states: checked, unchecked, and indeterminate
C. To increase the speed of data entry
D. To turn the checkbox into a radio button
Q8. Which values does Access use to store Yes, No, and Null for triple state checkboxes?
A. 1 for Yes, 0 for No, empty string for Null
B. "Yes" for Yes, "No" for No, "Null" for Null
C. -1 for Yes, 0 for No, Null for unknown
D. 1 for Yes, -1 for No, Null for unknown
Q9. How do you cycle through the three states of a triple state checkbox in a form?
A. Use a drop-down menu
B. Press the spacebar once
C. Click multiple times to toggle between yes, no, and unknown
D. Edit the value directly in the table
Q10. In which scenario would a standard Yes/No checkbox be a better fit than a triple state checkbox?
A. When you need to represent Yes, No, and Unknown
B. When the field must always be either Yes or No with no exceptions
C. When filtering by multiple categories
D. When data values are frequently changed by users
Q11. If you convert an existing Yes/No field to a Number (Long Integer) for triple state functionality, what should you also update?
A. Set the Default Value to Yes
B. Change all controls to text boxes
C. Remove the Default Value and set Required to No
D. Delete all existing records
Q12. What is a common use case for triple state checkboxes mentioned in the video?
A. Sorting data alphabetically
B. Filtering records based on criteria such as 'I don't care'
C. Formatting date fields
D. Exporting data to Excel
Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-B; 7-B; 8-C; 9-C; 10-B; 11-C; 12-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone covers how to work with triple state checkboxes in Microsoft Access, focusing on what triple state actually means and how it can be useful in your databases.
Stanley, one of my Platinum members, asked a great question about Yes No fields in his school database. He has several fields like IsParent, IsFaculty, and IsStudent. Sometimes he does not know the answer for a particular person, for example, whether someone is both faculty and enrolled in classes. With a regular Yes No field, the database assumes No if left blank, which can lead to incorrect data because we sometimes just do not know.
This brings up the importance of the third state, especially when you need to distinguish between No and Not Known. With triple state checkboxes, in addition to Yes and No, you have Null, which can represent I do not know, or in certain cases, I do not care (this can be handy in surveys). Null means the data has not been supplied, instead of forcing it into a Yes or No.
By default, a Yes No field in Access does not allow Null. You have to set up your field differently to permit this. For example, in a standard customer form, you might have a traditional Yes No field like IsActive. This is a field where Null would not be necessary because a customer is either active or not. However, in situations where you do not always know the answer, you need something more flexible.
To do this, go into your table in Design View and, instead of using the Yes No type, create a Number field with the Long Integer data type. Leave the Default Value blank so it is set to Null. Set Required to No so that you are not forced to pick a value. Do this for fields like IsParent, IsStudent, and IsFaculty.
On your form, after adding these fields as number fields, they will show up as text boxes by default. You will want to convert them to checkboxes for easy Yes/No/Null selection. You can copy an existing checkbox and update its Control Source property to bind it to the appropriate field, such as IsParent. Be sure to set the Triple State property of the checkbox to Yes. This will let you toggle between checked, unchecked, and an indeterminate state. The labels are just for display, so you can set them to whatever you like for clarity.
Once you have done this for each field, you can easily cycle through the three possible values on your form: click once for Yes, twice for No, three times for I do not know (Null). This is much more accurate when you are entering data that is sometimes unknown.
Triple state checkboxes are great for more than just data entry. I also use them for filtering in search forms, where you might want to find records that match Yes, No, or simply ignore a particular criteria if you do not care about it. If you use them for filtering, the underlying value in your table will be zero for No, negative one for Yes, or Null for unknown, which matches how Access stores Yes No values internally.
If you want to convert an existing Yes No field to a triple state capable field, change its data type to Number, ensure Required remains No, and remove any Default Value. This allows you to maintain your existing data while adding the ability to record an unknown state.
For more advanced uses of triple state checkboxes and search filtering, I cover these topics in detail in my Search Seminar. If you need step-by-step instructions, you can find a complete video tutorial on my website at the link below.
Live long and prosper, my friends.Topic List Definition of triple state checkboxes in Access
Difference between Yes No and triple state fields
Creating Number fields for triple state checkboxes
Setting Default Value to Null in table design
Setting Required property to No for triplet fields
Binding checkboxes to Number fields in forms
Enabling triple state in checkbox properties
Cycling through Yes No and Null states in forms
Copying and configuring multiple triple state checkboxes
Using triple state checkboxes for search filtering
Preserving data when converting Yes No to Number fields
Saving and displaying triple state values in Access |
