CODEWITHSUNDEEP
sqlms-accesswhere-clausebitwise-operators
Salman Arshad
Salman Arshad

Reputation: 272106

How to perform bitwise operations arithmetic in MS-ACCESS

Inside MSACCESS I want to use relatively simple bitwise operations in WHERE clause of queries such as this:

SELECT *
FROM Table1
WHERE Column1 (some operator) 8 = 0

This would:

PS: are bitwise operators different from boolean operations?

Upvotes: 4

Views: 9676

Answers (3)

HansUp
HansUp

Reputation: 97101

If you can run your query in in ANSI-92 Query Mode (e.g. by changing the Access UI Query Mode or by connecting to it using ADO classic or ADO.NET), use the BAND operator.

The following code sample prints this to the Immediate window:

8 AND 7: -1 
8 BAND 7: 0

The first case (AND) treats both numbers as True values, so True AND True gives -1 (True). I think the BAND approach is what you're after.

Public Sub BitwiseAndQuery()
    'the db engine treats numbers as booleans with AND '
    Debug.Print "8 AND 7: "; _
        CurrentDb.OpenRecordset("SELECT 8 AND 7")(0)

    'ADO includes BAND for bitwise AND '
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SELECT (8 BAND 7)", CurrentProject.Connection
    Debug.Print "8 BAND 7:"; rs(0)
    rs.Close
    Set rs = Nothing
End Sub

Upvotes: 4

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112372

In VBA, you can apply the Boolean operators to numbers in order to perform bitwise operations

(13 AND 8) = 0

In SQL, however, this does not work. However, you could write a VBA function that you call inside a query.

Upvotes: 1

vulkanino
vulkanino

Reputation: 9134

you could: 

WHERE (((column\(2^b)) mod 2) = 1)

edit: (where b is the specific bit to test)

OR is not available in Access unless you set ANSI Mode.

Upvotes: 7

Related Questions