Locked lesson.

Upgrade

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.

Exercise files

Download this lesson’s related exercise files.

Services_15_start.accdb
1.3 MB Services_15_YesNoDataType.accdb
1.3 MB

Quick reference

Yes/No (Boolean) Data Type and Checkboxes

Application Terminology

Analyzer

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.

http://analyzer.codeplex.com/

Checkbox

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

Null is the absence of a value.  Yes/No fields cannot represent Null.

Yes/No

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
http://allenbrowne.com/NoYesNo.html

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.

Steps

Change Display Control of a textbox to a check box in the Table Design

  1. Go to the Visual Basic Editor,
  2. Go to the Immediate Window,
  3. Type the following statement and press  (ENTER):

    CurrentDb.TableDefs("MyTable").Fields("MyField").Properties("DisplayControl") = CInt(acCheckBox)

    WHERE
    MyTable is the name of your table
    MyField is the name of an Integer field
    acCheckBox is a constant for the number 106
Login to download
  • 00:05 Hi, this is Access 2013, Lesson 15.
  • 00:09 I am Crystal.
  • 00:11 A data type in Access that is often misused
  • 00:14 is the yes/no data type, which can be displayed a checkbox.
  • 00:19 Yes/No can be just Yes or No.
  • 00:24 A common example of using a yes/no field is to add a checkbox
  • 00:28 for whether or not someone or something is active.
  • 00:32 For instance, an active employee, or an active participant.
  • 00:37 Go to the design view of the MyDataTypes table
  • 00:41 that we have been creating.
  • 00:43 Make a field called yn which is a Yes/No field.
  • 00:48 Set the Default Value to True.
  • 00:51 Save the table and switch to Datasheet View.
  • 00:54 Enter a number, 99, in either of the currency columns
  • 00:59 from our last lesson on the new record.
  • 01:02 Notice that as you begin creating a new record,
  • 01:05 the next new record appears on the bottom.
  • 01:08 The new records have the checkbox for yn checked
  • 01:12 but the records that were already created do not.
  • 01:16 We would have to specifically go check those.
  • 01:19 The Default Value only applies from this point forward.
  • 01:23 Here is a list of the yes/no fields in our Services database.
  • 01:28 This report came from the Analyzer,
  • 01:30 which is a free download from analyzer.codeplex.com --
  • 01:35 CodePlex is the sharing site for Microsoft.
  • 01:39 The current version of the Analyzer for Microsoft Access is also
  • 01:42 in the exercise folder (link in the Quick Reference Guide)
  • 01:45 In the Customers tables, there is a yes/no field called IsActiv.
  • 01:50 In simpler terms, this is an Active flag which enables you
  • 01:54 to mark as customer as being active or not being active.
  • 01:58 Click the Relationships icon on the Database Tools ribbon.
  • 02:02 Right-click on the customers table in the relationships window
  • 02:06 and choose Table Design.
  • 02:08 Click on the IsActiv field in the top pane.
  • 02:12 In the lower pane notice there are 3 choices for format:
  • 02:15 True/False, Yes/No, and On/Off
  • 02:20 On the Lookup tab, the Display Control is set to Check Box.
  • 02:26 Other choices are Textbox and Combo Box
  • 02:29 Close the Customers table.
  • 02:31 Later we will build a form to enter customers together.
  • 02:35 Then, when you create combo boxes with lists of choices,
  • 02:39 old customers that you no longer do business with
  • 02:42 can be filtered out by unchecking the active flag.
  • 02:47 In the Invoices tables, there are 2 yes/no fields.
  • 02:50 One is if the invoice is active --
  • 02:53 and this is whether or not it has been fully paid.
  • 02:56 Although the field is here,
  • 02:58 the logic is not implemented in these lessons.
  • 03:01 The other yes/no field is if the invoice is locked for editing.
  • 03:06 Once an invoice is submitted and recorded on the books,
  • 03:09 it shouldn't be changed.
  • 03:12 On the edit form, if the invoice is locked
  • 03:15 it can be seen but not changed.
  • 03:18 MyCompany is a table for storing
  • 03:20 the active company information
  • 03:22 so this database can more easily be shared and customized.
  • 03:27 While the logic is not implemented for IsActiv, the field is there
  • 03:31 in case you have a company with more than one location.
  • 03:35 Services are what you provide your customers
  • 03:38 and Service Types are a way to categorize the services.
  • 03:42 Perhaps you offer seasonal service types
  • 03:44 like Lawn Care or Snow Removal,
  • 03:46 where half the year, the choice would not be active --
  • 03:49 or you used to offer a service
  • 03:51 that turned out not to be profitable so you stopped.
  • 03:55 When work for customers is entered,
  • 03:57 the Services performed are also entered.
  • 04:00 When this data entry is done, it needs to be quick.
  • 04:03 Therefore choices on the list should be applicable.
  • 04:07 Having an Active flag on this record
  • 04:09 is more to allow for planning.
  • 04:12 Active flag is a term given to a checkbox for an Active status.
  • 04:18 For instance, the database can be used to plan future work
  • 04:22 and then when done, the Active flag can be set to True.
  • 04:26 Perhaps you were planning to trim bushes after mowing
  • 04:29 the big lawn for Alpha Associates but it started to rain,
  • 04:32 therefore that service was entered but didn't get done.
  • 04:36 You could uncheck the Active flag.
  • 04:38 A capability to copy inactive work
  • 04:40 to another work day could be built.
  • 04:43 Surely the sun will shine again
  • 04:45 and those bushes will need to be trimmed
  • 04:48 On forms, yes/no fields can be used to do things
  • 04:51 like lock editing after the time to edit has expired.
  • 04:56 A Yes/No field can be used to indicate
  • 04:58 if someone or something is active.
  • 05:02 Yes/No is not a good field choice when there is
  • 05:05 something more relevent that should be stored instead.
  • 05:08 For instance, a time when yes/no would not be a good choice
  • 05:13 is a check box if something was sent.
  • 05:16 It would be better to store the date something was sent
  • 05:19 and if the date has no value, then something was not sent
  • 05:22 and you can filter for the records that need a value.
  • 05:26 Boolean is the official name for the the data type for yes/no,
  • 05:30 and it cannot be nothing.
  • 05:32 The concept of no value is important
  • 05:35 and has a special name called Null.
  • 05:38 Yes/No can only be 1 of 2 values:
  • 05:41 yes or no, true or false, on or off, black or white ...
  • 05:46 there is no in between or shades of gray.
  • 05:50 In the next lesson, we will explore the date/time data type.

Lesson notes are only available for subscribers.

Gift this course