Multiple Selection Through Bitmasks
January 18, 2008
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 (05) 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.
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 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.