Bitfields : Intermediate Level Concept
Where It All Began
I remember when I first started using the Windows API calls in my databases. I was so excited about being able to do some cool things that were not native to Access and VBA. Things like displaying the ‘common dialogs’, getting system information and working with INI files (yes, I know that Access has ‘not-very-clearly-documented’ methods for calling some of the common dialogs). I found some examples by scouring the Internet and by purchasing Dan Appleman’s wonderful guide to the Windows API. As a newbie to API calls, I had not been exposed to bitfields, which allow you to use a single Long Integer value to represent several options. I can follow an example as easily as the next guy, so I was able to implement the API calls, but I have to admit that it took some time for me to finally wrap my brain around the concept of bitfields.
|
|
Overview And Purpose Of This Article
The purpose of this article is to help you understand what a bitfield is, why you would care to use them and how they work. That is, how they work in Access and VBA. I’m not going to get down to ones and zeros because VBA masks that level of detail and you don’t need it to use this technique effectively. KEY CONCEPT : as mentioned above, a bitfield is a single value (in VBA, typically LONG INTEGER) that can represent zero or more options. Here is an example: Your client has you working on a database that tracks the shipping schedule to customers. Your client ships 7 days a week, so you might be tempted to add 7 Yes/No fields to the customer table (one for each day of the week). While using 7 different fields will work, I believe that it is a better design to use one single field - a bitfield.
Understanding Your Options and Their Value
Table of options, bits and values for the shipping example:
| Day of Week |
Bit (x) |
Option Value (2x) |
| Sunday |
0 |
1 |
| Monday |
1 |
2 |
| Tuesday |
2 |
4 |
| Wednesday |
3 |
8 |
| Thursday |
4 |
16 |
| Friday |
5 |
32 |
| Saturday |
6 |
64 |
There are a couple of things to note about this table. There is nothing sacred about which bit goes with which option, it is up to you – as the programmer – to make that determination. The important thing is to assign a bit to each option, starting with bit zero and numbering the bits sequentially. Don’t skip bits. The VALUE of the option is calculated by raising two (2) to the power of the bit (i.e. value for Bit 0 = 20 = 1) (value for Bit 1 = 21 = 2) (value for Bit 5 = 25 = 32). If no bits are set, then the value of the bitfield will be zero. If you were using something other than the days of the week as your options, then your table might look something like this:
| Possible Options |
Bit (x) |
Option Value (2x) |
| Option 1 |
0 |
1 |
| Option 2 |
1 |
2 |
| Option 3 |
2 |
4 |
| Option 4 |
3 |
8 |
| etc., etc., etc. |
|
| |