want to use logic AND operator in query

[Hubert]

tried often, never succeded.

can anyone help with the following:

have a field(long) and want apply a filter like the
following:

progress AND 16

or

progress and 512
(where "progress" is the name of the field)
: a bítwise comparsion of the value of that field.

never worked for me in a query or as a filter

Any help much appreciated.

Thanks in advance

 

[Allen Browne]

Jet 4 (Access 2000 onwards) does have a BAND operator (Bitwise AND). From
memory, I suspect it works only in an ADO recordset, so you cannot use it in
the query design window.

Not too difficult to write your own for other conditions. This example uses
the Byte size:

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


--
Allen Browne - Microsoft MVP. Perth, Western Australia.


tried often, never succeded.

can anyone help with the following:

have a field(long) and want apply a filter like the
following:

progress AND 16

or

progress and 512
(where "progress" is the name of the field)
: a bítwise comparsion of the value of that field.

never worked for me in a query or as a filter

Any help much appreciated.

Thanks in advance

 

[John Mishefske]

Jet 4 (Access 2000 onwards) does have a BAND operator (Bitwise AND). From
memory, I suspect it works only in an ADO recordset, so you cannot use it in
the query design window.

Not too difficult to write your own for other conditions. This example uses
the Byte size:

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

I looked for MichKa's info on this undocumented Jet feature and he said it
works for OLE DB providers and not for ADO.

 

[Allen Browne]

I looked for MichKa's info on this undocumented Jet feature and he said it
works for OLE DB providers and not for ADO.

Interesting. It does work though:

Function TestBand()
Dim rs As New ADODB.Recordset
Dim strSql As String

strSql = "SELECT MyBitField, (MyBitField BAND 2) <> 0 As MyResult FROM
MyTable;"
rs.Open strSql, CurrentProject.Connection

Do While Not rs.EOF
Debug.Print rs!MyBitfield, rs!MyResult
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End Function

 

[John Mishefske]

Interesting. It does work though:

Function TestBand()
Dim rs As New ADODB.Recordset
Dim strSql As String

strSql = "SELECT MyBitField, (MyBitField BAND 2) <> 0 As MyResult FROM
MyTable;"
rs.Open strSql, CurrentProject.Connection

Do While Not rs.EOF
Debug.Print rs!MyBitfield, rs!MyResult
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End Function

Indeed it works great. I must have misunderstood. I was looking at this:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&safe=off&threadm=u56Gv5n6
%24GA.279%40cpmsnbbsa09&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DISO-8859-1
%26safe%3Doff%26q%3Dmichka%2Bband%2Bole%26btnG%3DGoogle%2BSearch%26meta%3Dgroup
%253Dcomp.databases.ms-access

No way around the wrap I guess.

 

[Hubert]

had done this operation before and couldnt get it to work

the filter looks like:
dfilter = "PRbitwise BAND " & Trim(str$(Me.Overdues)) & ")"
or
dfilter = "PRbitwise & " & Trim(str$(Me.Overdues)) & ")"

would look in your example like
dfilter = "BitwiseAndByte(" & Trim(str$(PRbitwise)) & ", "
& Trim(str$(Me.Overdues)) & ")"

in mine:
dfilter = "FCompareBits(" & Trim(str$(PRbitwise)) & ", " &
Trim(str$(Me.Overdues)) & ")"
but that doesnt work.

Thanks a lot, will find some solution
greetings Hubert

 

[Allen Browne]

The BAND operator probably won't work as a filter for a form or report, as
it does not work in the DAO context, and that's what Access natively uses.

It should not be too difficult to create a function suitable for your data
type though. I didn't understand why you are using the Str() function to
convert the bitfield values to string? It could be important that they are
passed into the function with the correct data type - whether Integer, Long,
or Byte - and that the function processes them as the correct type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


had done this operation before and couldnt get it to work

the filter looks like:
dfilter = "PRbitwise BAND " & Trim(str$(Me.Overdues)) & ")"
or
dfilter = "PRbitwise & " & Trim(str$(Me.Overdues)) & ")"

would look in your example like
dfilter = "BitwiseAndByte(" & Trim(str$(PRbitwise)) & ", "
& Trim(str$(Me.Overdues)) & ")"

in mine:
dfilter = "FCompareBits(" & Trim(str$(PRbitwise)) & ", " &
Trim(str$(Me.Overdues)) & ")"
but that doesnt work.

Thanks a lot, will find some solution
greetings Hubert