bitwise operations

[bill]

All,

I have not visited Access for a while, and I am drawing a blank on how
to search/sort a column of data (integer) for/on a particular bit
pattern. Actually, a SQL example would be great.
TIA,
Bill

[Lyle Fairfield]

My !!!!vague!!!! recollection is that one must use OLEDB to effect
bitwise operations in Access/Jet SQL regardless of the Ansi 92 setting.
ADODB is of course an OLEDB route, or vehicle.

As I recall the first of these works correctly, while the second does
not:

Private Sub ADODBForBitWiseQuery()
Dim r As ADODB.Recordset
With CurrentProject.Connection
Set r = .Execute("SELECT ID, ID BXOR 2 as IDBXORedWith2 from
Table4")
MsgBox r.Collect(0) & ":" & r.Collect(1) & ":" &
CStr(r.Collect(0) Xor 2)
End With
End Sub

Private Sub DAOForBitWiseQuery()
Dim r As DAO.Recordset
With DBEngine(0)(0)
Set r = .OpenRecordset("SELECT ID, ID XOR 2 as IDXORedWith2
from Table4")
MsgBox r.Collect(0) & ":" & r.Collect(1) & ":" &
CStr(r.Collect(0) Xor 2)
End With
Set r = Nothing
End Sub

You could test them by running the SQL on any table with a whole number
ID field, changing the name of the table. Of course, you could use
another field (name) as well.

But I have not studied this extensively. It is entirely possible that a
DAO solution will be posted.

[Lyle Fairfield]

My !!!!vague!!!! recollection is that one must use OLEDB to effect
bitwise operations in Access/Jet SQL regardless of the Ansi 92 setting.
ADODB is of course an OLEDB route, or vehicle.

As I recall the first of these works correctly, while the second does
not:

Private Sub ADODBForBitWiseQuery()
Dim r As ADODB.Recordset
With CurrentProject.Connection
Set r = .Execute("SELECT ID, ID BXOR 2 as IDBXORedWith2 from
Table4")
MsgBox r.Collect(0) & ":" & r.Collect(1) & ":" &
CStr(r.Collect(0) Xor 2)
End With
End Sub

Private Sub DAOForBitWiseQuery()
Dim r As DAO.Recordset
With DBEngine(0)(0)
Set r = .OpenRecordset("SELECT ID, ID XOR 2 as IDXORedWith2
from Table4")
MsgBox r.Collect(0) & ":" & r.Collect(1) & ":" &
CStr(r.Collect(0) Xor 2)
End With
Set r = Nothing
End Sub

You could test them by running the SQL on any table with a whole number
ID field, changing the name of the table. Of course, you could use
another field (name) as well.

But I have not studied this extensively. It is entirely possible that a
DAO solution will be posted.

[Allen Browne]

VBA supports bit operatations, such as converting to upper case (turning a
bit on) with:
vbKeyA OR &H20
or forcing to lower case (turning a bit off) with:
97 AND NOT &H20

Before Access 2000, JET SQL never supported bit operations. The new binary
JET SQL operators are not well documented, but they start with B, e.g. BAND
for Binary AND, BOR of Binary OR, etc. Trouble is, this only works if you
run the query under ADO. Since the Access query interface uses DAO, you
cannot do this in a normal Access query, only programmatically. This example
flips all the bits in the integer field MyIntFlip:

Function TestBnot() AS Long
Dim strSql As String
strSql = "UPDATE MyTable SET MyIntFlip = BNOT MyInt WHERE MyIntFlip Is
Not Null;"
CurrentProject.Connection.Execute strSql, lngKt
TestBnot = lngKt
End Function

A workaround if you need it in DAO or in the interface or Access 97 is to
call VBA functions to perform the binary operations. This example tests the
result of an AND operation on two bytes, so you can pass the fields into the
function and display the result by typing this calculated field into your
query:
MyResult: BitwiseAndByte([Byte1], [Byte2])

Function BitwiseAndByte(varByte1 As Variant, varByte2 As Variant) As Boolean
On Error Resume Next
BitwiseAndByte = CByte(vByte1) And CByte(vByte2)
End Function

(The function accepts variants to support non-required fields.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"bill" <wg****@draper.com> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...

I have not visited Access for a while, and I am drawing a blank on how
to search/sort a column of data (integer) for/on a particular bit
pattern. Actually, a SQL example would be great.