About bit Operate in access

[GG]

hi, i make a query in SQL SERVER like this: SELECT * FROM
TABLENAME WHERE (FIELDNAME & 8)= 8, that return the rows
that FIELDNAME's bit3 is 1, and it works well. BUT in
ACCESS , it does not work well. HOW can i implement a same
query in ACCESS just like it in SQL SERVER?

 

[Allen Browne]

If the data is in SQL Server, you could use a pass-through query.

If not, and you are working with Access 2000 or later, you could
OpenRecordset in ADO and use the BAND operator.

If you are trying to do this through the interface or with an earlier
version, then you must write a VBA function to perform the bitwise
operation, and call the function in your query.

 

[Gary Walter]

hi, i make a query in SQL SERVER like this: SELECT * FROM
TABLENAME WHERE (FIELDNAME & 8)= 8, that return the rows
that FIELDNAME's bit3 is 1, and it works well. BUT in
ACCESS , it does not work well. HOW can i implement a same
query in ACCESS just like it in SQL SERVER?

Hi GG,

In Access, the ampersand is equivalent
to "+" in SQL Server.

What you use in your query
(as I understand it), depends on
how you are running the query.

ADO
--use the BAND operator
strSQL = "SELECT * FROM TABLENAME " _
& "WHERE ([FIELDNAME] BAND 8)= 8"
CurrentProject.Connection.Execute strSQL, dbFailOnError

DAO/Query Designer
--define a BitAnd function (save in a module)

Public Function BitAND (p1 As Long, p2 As Long) As Long
BitAND= p1 AND p2
End Function

SELECT * FROM TABLENAME
WHERE BitAND([FIELDNAME], 8)= 8

Please respond back if I have misunderstood.

Good luck,

Gary Walter

 

[Allen Browne]

BAND is not a function. It's an operator - Bitwise-AND. There is also a BOR,
BXOR, etc.

These operators are limited to JET4, so can only be used through ADO.
Example:

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

 

[GG]

How about OLE DB?

-----Original Message-----
BAND is not a function. It's an operator - Bitwise-AND. There is also a BOR,
BXOR, etc.

These operators are limited to JET4, so can only be used through ADO.
Example:

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

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





.

 

[GG]

How about OLE DB?

-----Original Message-----
BAND is not a function. It's an operator - Bitwise-AND. There is also a BOR,
BXOR, etc.

These operators are limited to JET4, so can only be used through ADO.
Example:

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

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





.