Trouble with bit arithmetic in SQL 1

[VickyC]

hello again

I have a table...[tt]

ID Rating
1 56
2 17
3 44
4 11

...etc [/tt]

The MAXIMUM value of Rating is 63, so each Rating can be written as 6 bits. (ie: 56 = 111000). I want to write a query that shows the six bits for each Rating. Like this... [tt]

ID Rating b32 b16 b8 b4 b2 b1
1 56 1 1 1 0 0 0
2 17 0 1 0 0 0 1
3 44 1 0 1 1 0 0
4 11 0 0 1 0 1 1

...etc [/tt]

Now, when I type ? (56 AND 32) = 32, for example, in the immediate window, I get TRUE. And, when I type ? (56 AND 4) = 4, for example, I get FALSE as expected. My problem is that I can't get the SQL to work. when I try...

SELECT ID, Rating, ((Rating AND 32) = 32) AS b32, ((Rating AND 16) = 16) AS b16, etc....

... I get 0's in all the 'b' fields. I can't get both 1's and 0's to show as in the query output shown above.

Thanks for any clues.
Vicky C.

 

[PHV]

SQL use a logical AND, VBA a bitwise AND.
In a standard module create a function like this:

Function myAnd(op1, op2)
If IsNumeric(op1) And IsNumeric(op2) Then
    myAnd = (op1 And op2)
End If
End Function

And then your query:

SELECT ID, Rating, Abs(myAnd(Rating,32)=32) AS b32, Abs(myAnd(Rating,16)=16) AS b16, Abs(myAnd(Rating,8)=8) AS b8, Abs(myAnd(Rating,4)=4) AS b4, Abs(myAnd(Rating,2)=2) AS b2, Abs(myAnd(Rating,1)=1) AS b1
FROM tblRating

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

Thanks again PHV - a clear explanation, as usual! I would never have realized that AND was being used in two different senses. The solution works perfectly.

Vicky C.

 

[strongm]

>SQL use a logical AND

Actually, blame the fact that Access uses DAO for the query interface. SQL happily supports binary operations, even Access's version of SQL (BAND, BOR, BXOR). But DAO doesn't. Only ADO. And only on Access 2000 and later

For example, the following in a module works the way you might expect:

[blue]Public Sub spoon()
    With New ADODB.Recordset
        .Open "SELECT (56 BAND 32)=32 AS Result;", CurrentProject.Connection
        MsgBox !Result
    End With
End Sub[/blue]

 

[MazeWorX]

and only in vba

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[strongm]

Actually, in any language capable of using the ADO COM libraries