About this lesson
Best practices for using the Yes/No Data Type, which can be displayed as a check box to show when something is active.
Download this lesson’s related exercise files.Services_15_start.accdb
1.3 MB Services_15_YesNoDataType.accdb
Yes/No (Boolean) Data Type and Checkboxes
Download the free Analyzer for Microsoft Access from CodePlex, Microsoft's sharing site. This tools, written for Access, is updated a few times a year by several developers, so it is best to go there and get the latest version.
A checkbox control allows you to check or clear a value.
If you are using a checkbox for a value that does allow Null to be represented and there is no value, the checkbox will appear with a black square in the middle.
Null is the absence of a value. Yes/No fields cannot represent Null.
The Yes/No data type in Access can only be one of 2 possible values:
- -1 represents True, or Yes, or On
- 0 represents False, or No, or Off
Internally, the Yes/No data type is stored as a 8-bit signed number except the capabilities of its storage have been crippled to not to allow any value except -1 or 0. Yes/No cannot represent null, or nothing; it must be one of the 2 values.
Unlike the VBA Boolean data type, however, it is actually possible to return null for a yes/no field when joining tables. Null means no value, and can cause issues as the JET engine in Access assumes that a Yes/No field will always be -1 or 0 and will never be null.
As a workaround, Allen Browne suggests using Integer and assigning a checkbox as the display control.
Why I stopped using Yes/No fields, by Allen Browne
Many times, however, instead of storing Yes or No, something else should be stored. For instance, instead of a checkbox if something was received or not, it is better to use a date field and store WHEN an item was received. If the date field is not filled, it can then be assumed the item has not yet been received.
Change Display Control of a textbox to a check box in the Table Design
- Go to the Visual Basic Editor,
- Go to the Immediate Window,
- Type the following statement and press (ENTER):
CurrentDb.TableDefs("MyTable").Fields("MyField").Properties("DisplayControl") = CInt(acCheckBox)
MyTable is the name of your table
MyField is the name of an Integer field
acCheckBox is a constant for the number 106
Lesson notes are only available for subscribers.