Bitwise operations in MS access

Question

I have a table which contains an answer. This answer is a combination of a set list of numbers. (see below)

Possible values

2 4 8 16 32 64

If I have the answer of Answer 24 – I need some method to work out the only values that could have been selected are 16 and 8. I am told if this where SQL I would use a bitwise operation.

I am fairly advance in Access but tend not to use VBA code, but am open to all ideas.

Answer 1

Compare KB194206 (the gist of it: "The Microsoft Jet database engine does not support bitwise operations in SQL. This behavior is by design."), which basically means you're stuck with VBA.

And bitwise in VBA is very simple, because in VBA all locical operators are bitwise.

Just create a wrapper function:

Function BitAnd(Value1 As Long, Value2 As Long) As Long
    BitAnd = Value1 And Value2
End Function

and use it in your SQL

WHERE
    BitAnd([someColumn], 64) > 0

Do the same thing for the other bitwise operations you need.

---

You could use it in joins, as well. Assume you have an FlagsTable (FlagValue, FlagName) that contains the names for each of your available flags:

SELECT
  d.Id,
  d.BitField,
  f.FlagName
FROM
  DataTable d
  INNER JOIN FlagsTable f ON BitAnd(d.BitField, o.FlagValue) > 0

This query would be one way to address your "24 consists of 16 and 8" sub-question.