How do I do bitwise compare operations on a field?

[Guest]

When I use AND or Xor on a numeric field in an Access Query, it appears not
to do the bitwise compare, but rather takes 0 as False and anything else as
True and does a general And operation. For example, on the Query grid:
Expr1: (CInt(1) And CInt(2)) always returns -1: as in True And True, whereas
in VBA x = 1 And 2 will return 0 to x. x = 3 And 2 will return 2. It's as if
it's doing a CBool on each side of the And before doing the And operation.

Any ideas how to get it to do a binary bitwise compare?

 

[Marshall Barton]

When I use AND or Xor on a numeric field in an Access Query, it appears not
to do the bitwise compare, but rather takes 0 as False and anything else as
True and does a general And operation. For example, on the Query grid:
Expr1: (CInt(1) And CInt(2)) always returns -1: as in True And True, whereas
in VBA x = 1 And 2 will return 0 to x. x = 3 And 2 will return 2. It's as if
it's doing a CBool on each side of the And before doing the And operation.

You got it exactly right.

I thing you will have to create some VBA functions to
perform the binary operations.

Public Function MyAnd(x As Long, Y As Long) As Long
MyAnd = x And Y
End Function

 

[Guest]

If you are willing to switch to ADO/ANSI/ODBC/CurrentProject
syntax, you can use BAND instead of AND.

Or you can use mod and \ to create arithmetic expressions that
evaluate individual bits.

Or you can use YesNo fields: those are bits, and are stored compactly
in Jet databases.

(david)

 

[Guest]

BAND sounds good. I am actually using ADO with VBscript. I just tried BAND
instead of AND in the SQL statement, but it didn't work:
SELECT ([MyTable].[MyField] BAnd 1) AS MaskedNumber ...
gives error:
Syntax error (missing operator) in query expression '([MyTable].[MyField]
BAnd 1)'

How do you use BAND? I couldn't find it on the MSDN site.

Thanks

 

[david epsom dot com dot au]

Jet has two versions of SQL syntax, sometimes called
'Jet SQL' and 'SQL Server SQL'. BAND is only valid
in the 'SQL Server SQL'. 'SQL Server SQL' is what I
get when I do a select query against a Jet table on
an ADO connection:

?currentproject.Connection.Execute("select (idxReport band 1) as fred from
tblRP_ReportMain").Fields(0)

But if you query against a 'Jet SQL' view, you may in
some circumstances get 'Jet SQL' syntax instead? In any
case, I can't see what you are doing wrong: it works for
me.

(david)

BAND sounds good. I am actually using ADO with VBscript. I just tried BAND
instead of AND in the SQL statement, but it didn't work:
SELECT ([MyTable].[MyField] BAnd 1) AS MaskedNumber ...
gives error:
Syntax error (missing operator) in query expression '([MyTable].[MyField]
BAnd 1)'

How do you use BAND? I couldn't find it on the MSDN site.

Thanks

If you are willing to switch to ADO/ANSI/ODBC/CurrentProject
syntax, you can use BAND instead of AND.

Or you can use mod and \ to create arithmetic expressions that
evaluate individual bits.

Or you can use YesNo fields: those are bits, and are stored compactly
in Jet databases.

(david)

 

[Michel Walsh]

Hi,



? CurrentProject.Connection.Execute( "SELECT 12 BAND 5").Fields(0).Value
4


You have to use ADO. That won't work with DAO or with the Query Designer (by
default, at least).



Hoping it may help,
Vanderghast, Access MVP

BAND sounds good. I am actually using ADO with VBscript. I just tried BAND
instead of AND in the SQL statement, but it didn't work:
SELECT ([MyTable].[MyField] BAnd 1) AS MaskedNumber ...
gives error:
Syntax error (missing operator) in query expression '([MyTable].[MyField]
BAnd 1)'

How do you use BAND? I couldn't find it on the MSDN site.

Thanks

If you are willing to switch to ADO/ANSI/ODBC/CurrentProject
syntax, you can use BAND instead of AND.

Or you can use mod and \ to create arithmetic expressions that
evaluate individual bits.

Or you can use YesNo fields: those are bits, and are stored compactly
in Jet databases.

(david)

 

[Guest]

Thank to this thread I created the query I need!
In Module1:

Public Function BandAid(ByVal P1 As Long, ByVal p2 As Long) As Boolean
BandAid = (P1 And p2) = p2
End Function

Field In Query1:

BitwiseInspection: BandAid([ScopeLongInt],[ItemLongInt])

The "BitwiseInspection" Field correctly returns
True for 48,16 and 48,32
False for 48,[1-15,17-31,33...]

! Watch out for Null and Zero

Thanks Friends