Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. FIX: Jet drivers do not support bitwise operators

FIX: Jet drivers do not support bitwise operators

View products that this article applies to.

This article was previously published under Q194206

↑ Back to the top

Symptoms

The Microsoft ODBC Driver for Access and the Microsoft OLE DB Provider for Jet do not provide support for bitwise operations in SQL statements. Attempts to use AND, OR, and XOR with numeric fields in a SQL statement return the result of a logical operation (true or false).

↑ Back to the top

Cause

The Microsoft Jet database engine does not support bitwise operations in SQL.

↑ Back to the top

Resolution

Bitwise operations must be replaced with the equivalent mathematical expressions or performed on the data outside of a SQL statement (performed in Visual Basic for Applications code for example).

↑ Back to the top

Status

This behavior is by design.

↑ Back to the top

More information

You can test for a single bit set in a long integer or integer field using the following algorithm:
( <value> \ (2^<bit>) ) mod 2
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:
iif( <value> < 0, 1, 0 )
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

↑ Back to the top

Keywords: kbbug, kbfix, KB194206

↑ Back to the top

Article Info
Article ID : 194206
Revision : 4
Created on : 12/9/2004
Published on : 12/9/2004
Exists online : False
Views : 573