About bit Operate in access

  • Thread starter Thread starter GG
  • Start date Start date
G

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?
 
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.
 
GG said:
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?
Click to expand...
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
 
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
 
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.





.
Click to expand...
 
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.





.
Click to expand...
 
Back
Top