Ever wish you could store multiple selections to a single table column? The solution, Bitmask Fields, have been in use for decades. Follow along as Danny Lesandrini shows you how to unlock the power of two.

Let me begin by revealing that I'm not an expert with bitmasks. However, this fact should bring a little comfort to the uninitiated because it means that anyone can implement multiple selections using a bitmask field and operators. Odds are, you've been using them for years, but simply didn't notice it. If you've ever set the properties of a message box using Intellisense, then you've used bitmasks.

Notice the screen shot of the Microsoft VBA MsgBox function in action, adorned with all its Intellisense regalia. When you type the first comma, a drop-down list of options appears. If you check the help file, you'll find the table of options shown below, along with their constant values. If we select vbCritical, the value used is 16, and the Critical Stop icon is added to the message box.

As you may be aware, there are more options than the ones I show below. In addition the help file for the MsgBox() function provides these helpful instructions:

The first group of values (0–5) describes the number and type of buttons displayed in the dialog box; the second group (16, 32, 48, 64) describes the icon style; the third group (0, 256, 512) determines which button is the default; and the fourth group (0, 4096) determines the modality of the message box. When adding numbers to create a final value for the buttons argument, use only one number from each group.

That's basically how a bitmask works ... it's singular value that is made up of the addition of other distinct values. This example, however familiar, is not what I consider to be a pure example because it comes with the instruction disclaimer above. In a perfect world, the sum of two bitmask values can NEVER equal another value in the list. Thus, pure binary values are usually employed. See the example in the next section.

Bitmask Demo Example

While the idea behind bitmasks can seem a little convoluted, the code is very simple. Download the sample application and you'll see what I mean. It solves the following problem:

    How do I save a number of Boolean attributes for an employee?

More specifically, as shown in the form below, how do I save user selections for the FIVE attributes shown here: Full Time, Hourly, Union, Management, Amiable? These options are not mutually exclusive. An employee could possess one or many, all or none of these attributes. An employee could be Hourly and be in Management. They could belong to the Union and they might even be Amiable. Or they could possess none of those attributes.

Traditionally, we are inclined to create a field of the table for each checkbox. But what happens when you come up with another attribute, like [Terminated]? Back to the table, right? Not necessarily. If you're using a bitmask, you simply add another attribute and code for it.

I've saved the bitmask options to a table named attribute but strictly speaking this table of attributes isn't necessary for this code, but in a SQL Server application where we use bitmasks, a table of bitmask attributes is maintained and used in queries to filter results. In the sample code below, we simply show how to display and save edits. A simple Access query is included, but no advanced techniques. Let's consider the code.

Coding the Bitmask

Before we consider the demo code, a word about logical operators. The And logical operator is used to determine if a particular bit should be toggled. If the field value contains the bit we're looking for, it returns that bit's value. If not, it returns zero. It works something like this:

    1 AND 1 = 1
    1 AND 2 = 0
    1 AND 4 = 0
    
    2 AND 1 = 0
    2 AND 2 = 2
    2 AND 4 = 0
    
     ' Set the checkbox to a Boolean expression ...
    Me!chk01 = (lngEmpAttributes And 1) = 1

When saving the selected options back to the database, the Or logical operator is used along with a variable of type Long Integer. Notice that this doesn't behave the same as addition (1+1), which would yield a result of 2. As shown below, 1 Or 1 yields 1 while 1 Or 2 produces a sum of 3. This prevents an overload of operators if the value is Or'ed more than once.

    1 Or 1 = 1
    1 Or 2 = 3
    1 Or 4 = 5
    
    2 Or 1 = 3
    2 Or 2 = 2
    2 Or 4 = 6
    
     ' If checked, add the bit to lngTotal
    If Me!chk01 = True Then lngTotal = lngTotal Or 1

Below is the full code listing, but other than what's described above, it contains only some code to toggle the font color of the labels to illustrate the action in the user interface. This is a simple, elegant and extensible solution for maintaining a variable number of attributes!

Option Compare Database
Option Explicit

Private Sub Form_Current()
On Error Resume Next

    Dim lngEmpAttributes As Long

    ' Grab the value for emp_attributes from the bound field
    ' If NULL, then use ZERO as a value (uninitialized)
    lngEmpAttributes = Nz(Me!txtEmp_attributes, 0)
    
    ' Set the True/False bit of each of our 5 checkboxes
    Me!chk01 = (lngEmpAttributes And 1) = 1
    Me!chk02 = (lngEmpAttributes And 2) = 2
    Me!chk04 = (lngEmpAttributes And 4) = 4
    Me!chk08 = (lngEmpAttributes And 8) = 8
    Me!chk16 = (lngEmpAttributes And 16) = 16

    ' Toggle the color of the labels for demo clarity
    Call UpdateBitmaskLabels

End Sub

Public Function UpdateEmployeeAttributes() As Boolean
On Error Resume Next

    Dim lngTotal As Long
    
    ' For each check box, add its value if checked
    ' (this could be handled with a For Each loop if
    '  the options were numerous, as with States)
    If Me!chk01 = True Then lngTotal = lngTotal Or 1
    If Me!chk02 = True Then lngTotal = lngTotal Or 2
    If Me!chk04 = True Then lngTotal = lngTotal Or 4
    If Me!chk08 = True Then lngTotal = lngTotal Or 8
    If Me!chk16 = True Then lngTotal = lngTotal Or 16
    
    ' Update the database field with the new total
    Me!txtEmp_attributes = lngTotal
    
    ' Toggle the color of the labels for demo clarity
    Call UpdateBitmaskLabels
    
End Function

Private Function UpdateBitmaskLabels() As Boolean
On Error Resume Next

    ' Reset all label text forecolor to black
    Me!lbl01.ForeColor = vbBlack
    Me!lbl02.ForeColor = vbBlack
    Me!lbl04.ForeColor = vbBlack
    Me!lbl08.ForeColor = vbBlack
    Me!lbl16.ForeColor = vbBlack
    
    ' Toggle forecolor to red where boxes are checked
    If Me!chk01 = True Then Me!lbl01.ForeColor = vbRed
    If Me!chk02 = True Then Me!lbl02.ForeColor = vbRed
    If Me!chk04 = True Then Me!lbl04.ForeColor = vbRed
    If Me!chk08 = True Then Me!lbl08.ForeColor = vbRed
    If Me!chk16 = True Then Me!lbl16.ForeColor = vbRed

End Function

The catch, if there is one, has to do with querying. There are some advanced techniques that work in SQL Server, but Jet doesn't support bitwise comparisons directly. You can, however, create a VBA function to do the comparison and call it from a query. That's what was done to return these results. The function used is displayed below.

Public Function EvalBit(ByVal varBitmask As Variant, lngCompare As Long) As String
On Error Resume Next

    Dim lngBitmask As Long
    
    If IsNumeric(varBitmask) Then
        lngBitmask = CLng(varBitmask)
    Else
        lngBitmask = 0
    End If
    
    If (lngBitmask And lngCompare) = lngCompare Then
        EvalBit = "Yes"
    Else
        EvalBit = ""
    End If
    
End Function

Bitwise Power of Two

Even though the Microsoft MsgBox() function seems to contradict my assumption, well-formed bitmasks rely on the power of two. That's how I've always used them, and that's how it's described out at The Access Web, in this article:
   The Access Web: Bits and Bitmasks

The Access MVPs do a better job than I could of explaining the binary aspect, and they give a similar example to what I've outlined above. If you're still confused about Bitmasks, take a look at the article cited above. While it may not be the perfect solution for every multi-select problem, it's certainly something to keep in your back pocket.

» See All Articles by Columnist Danny J. Lesandrini