Saturday, March 14, 2020

Bitwise Operation in an Access Query

Let me preface this article by saying this is not a topic I am really familiar with. I only decided to write it to share an experience I had when I recently participated in a forum thread discussion on this subject. So, if I misspoke or provided inaccurate information below, please do not hesitate to let me know. I would really appreciate it.

If you are not familiar with "bitwise operation," it is basically a way to perform value calculations or comparisons at the bit level. For example, one might need to use bitwise operation to check if a particular bit within a value is either ON (1) or OFF (0). In essence, performing a bitwise operation is a way to determine the state (1 or 0) of each individual bit of a particular value.

There are [as far as I know] three bitwise operators: AND, OR, and XOR

The AND operator returns a value of 1 if both the bits being compared are 1.

The OR operator returns a value of 1 if either of the bits being compared is a 1.

And the XOR operator returns a value of 1 if, and only if, one of the bits being compared is a 1.

Okay, so how do we normally perform a bitwise operation in Access. Well, we would usually use VBA for this. For example, the following function can be used to perform an AND bitwise operation.

Public Function bitwiseAND(var As Long, bit As Long) As Boolean
'thedbguy@gmail.com
'3/14/2020
'Returns True if the bit being tested is ON

bit = 2 ^ bit

bitwiseAND = var And bit

End Function

To check if bit position #3 is ON (remember, bit position is zero-based), we might do something like:

?bitwiseAND(45,2)
True

The above function returned True because 45 is represented as 101101 in binary, and the third bit from the right is a 1.

I hope the above example is clear. So, as you can see, we can use VBA in Access to perform bitwise operation. However, the topic of this article is how can we do it in an Access query? Obviously, we can use the above function in a query, but it would have been better if we can simply use the bitwise operators (AND, OR, and XOR) in SQL. Unfortunately, not in Access. However, the bitwise operators are available in T-SQL. So, one potential solution, if someone needs to perform bitwise operation against multiple sets of data in a query is to migrate the data into SQL Server.

Okay, now we come to the point of this article. So, a forum poster asked how to perform a bitwise AND operation in an Access query because, for some reason or another, they don't want to use VBA and migrating to SQL Server is not an option. I did a little research and came up with the following alternate solution.

([FieldName]\2^BitPosition) Mod 2

In the above expression, we perform an integer division against a numeric field using the decimal value of the bit position we want to check and then check if the result is divisible by 2. If the bit position being checked is ON, then the result of this expression will be a 1. If the bit is OFF, then the result will be a 0.

And there you have it. You can use the above expression in an Access query to check if a bit is on or off. I can't think of a good example or a situation where this solution would apply, so please let me know if you have any situation where this information could be useful.

As always, thanks for reading...

No comments:

Post a Comment