One of my long standing frustrations with Microsoft Access is the way it handles the “Yes/No” data type. On the surface, it seems perfect for building a Yes/No option group on a form. And to be fair, it works well as long as you are comfortable with always having a value selected.
The problem appears when you need a third state: “Null”. In many real world scenarios, you do not want to assume Yes or No by default. Instead, you want the field to remain blank until the user makes a conscious choice. This triple state requirement, meaning Yes, No, or Not Yet Answered, is surprisingly common.
Unfortunately, a standard Yes/No field in Access does not allow Null values. It must always contain either True or False. That makes it unsuitable when you want the option group to start out blank and only be populated after user input.
The good news is that the solution is simple!
Instead of using the Yes/No data type, change the field data type to “Number” and then adjust the “Field Size” to match your needs.
For example, if you prefer to represent:
- Yes as “-1”
- No as “0”
set the Field Size to “Integer”. This approach allows the field to accept Null values while still supporting your Yes and No logic.
By switching from Yes/No to Number, you gain control over that third undecided state without losing any functionality in your option group. Sometimes a small structural change is all it takes to make Access behave the way you expected it to from the beginning.
Bonus: This approach also allows for upsizing to other RDMSs if you need to down the road.
Oh my goodness! Thank you for explaining this. I’ve been sitting at my desk for the past 4 hours trying to figure out what was wrong with my yes or no check boxes! Thank you!
This is a great way to do it. I have done it too. Only what happens if the user accidently clicks the Yes/No option group ? Then they want to return it to null because they don’t know the answer ?
I would suggest a double click event on the option group frame, which sets it to null.
Because such hidden double-click events are not intuitive to users, I would typically suggest simply adding a ‘clear’ button that would set the field value to Null.