FIX: Jet drivers do not support bitwise operators (194206)

MORE INFORMATION

You can test for a single bit set in a long integer or integer field using the following algorithm: This expression will return 1 if the <bit> is set and 0 if <bit> is not set. <bit> is numbered from 0 to 30 inclusive where <bit>=0 is the first bit. <bit> values greater than 30 will not work with this algorithm because Jet uses unsigned long integer values and 2^31 is one larger than the largest unsigned long integer value and thus will cause numeric overflow when the division is evaluated.

Note that the \ operator and not the / operator is used. The \ operator is used for integer division. The / operator is used for floating point division and will cause unexpected results when used with this algorithm.

You can check <bit> 31 for a long integer using the following algorithm: This works because an unsigned long integer that is less than zero means the highest order bit (bit 31 for a long) is set.

Suppose you have a table named Test and a long integer field named TestFlags. You can use the following SQL statements to test to see if the bit 11 is set in the TestFlags field

SELECT * FROM Test WHERE ([TestFlags]\2^11) mod 2 = 1
				

or replace 2^11 with 2048 to save some query calculation time:

SELECT * FROM Test WHERE ([TestFlags]\2048) mod 2 = 1
				

You can use the following SQL to test for bit 31

SELECT * FROM Test WHERE iif( [TestFlags] < 0, 1 ,0 ) = 1
				

but this SQL statement would be a much more efficient test for bit 31:

SELECT * FROM Test WHERE [TestFlags] < 0
				

Note that you can also create calculated columns in SQL to display the results of one or more bit checks:

SELECT ([TestFlags]\2^11) mod 2 AS Bit11Set FROM Test
				

You can run the following ADO code to verify that this algorithm works correctly over various ranges and with various bit flags. Note the test requires a blank Microsoft Access database named C:\Db1.mdb and a reference to Microsoft ActiveX Data Objects.

   ' START SAMPLE CODE
   Sub VerifyBitTest()
   Dim i As Long, min As Long, max As Long, bit As Long
   Dim conn As New ADODB.Connection
   Dim rs As New ADODB.recordset

      conn.Open "DRIVER=Microsoft Access Driver (*.mdb);" & _
      "MAXBUFFERSIZE=128;DBQ=c:\db1.mdb"
      On Error Resume Next
         conn.Execute "DROP TABLE Test"
      On Error GoTo 0
      conn.Execute "CREATE TABLE Test (TestFlags LONG)"
      conn.Execute "INSERT INTO Test (TestFlags) VALUES (0)"
      min = 2 ^ 0: max = 2 ^ 30: bit = 11
      For i = min To max ' This could take a while.
         rs.Open "SELECT (" & i & "\2^" & bit & _
                 ") mod 2 AS BitSet FROM Test", conn
         If rs!BitSet <> IIf((i And (2 ^ bit)) > 0, 1, 0) Then
            MsgBox "Bit Test Failure!"
            Exit Sub
         End If
         rs.Close
         DoEvents
         If i Mod 100 = 0 Then Debug.Print "Verified " & i & " of " & max
      Next i

   End Sub
   ' END SAMPLE CODE