Bitwise AND does NOT WORK in data source line or eval()

To do bitwise AND you have to use something like the following

Bit        Value
0            1  
1            2
2            4
3            8
4            16
and so on..

bit 1 is turned on when
((MyFlagValue /2^1) MOD 2) = 1

bit 1 is not on when
((MyFlagValue /2^1) MOD 2) = 0


bit 4 is turned on when
((MyFlagValue /2^4) MOD 2) = 1

bit 4 is not turned on when
((MyFlagValue /2^4) MOD 2) = 1

So say you have a flag field called TestFlag
TestFlag has a value of 17.
You want to find out if the 4th bit is on. Normally you would test as follows

If ((TestFlag AND 16) = 16) Then....

If that expression is True then the bit is on

In Access querys and such you need to use

IIF( (([TestFlag] /2^4) MOD 2) = 1 , True, False)

Here is a simpler way that does not depend on you knowing in advance which bit you want to examine (so you don't need to do that 2^x division)

Simply stick this simple function in a standard module...

Function bAND(n1 As Long, n2 As Long) As Long
    bAND = n1 And n2
End Function

Then in queries you will be able to use
expressions like...

BitwizeCompare: bAND([OneField],nSomeValue)

and in forms you can say

MyControl = bAND([SomeControl],[SomeOtherControl])

in either case bAND will return the result of a binary AND. The same tecnique can be used for the other logical operators.

That's exactly right, Lambert,

Since I'm using a mask for the relevant bits anyway, there is no shifting needed.  I do not need to know what bit is on, just whether any bits in the mask is on.  That's one reason you use a mask.  I got around the problem of the AND not behaving bitwise with a function identical with yours above before I asked the question.

The real problem I see is that AND works two different ways depending on where it is and what it's operating on.  In pure VB it works as advertised (else the function wouldn't work), but in a report it doesn't (necessitating the function).  Even in an Eval() it reverts back to it's  logical use, fer g'ness sakes!  These things should not be!  

I was really wondering if
1)anyone had noticed this; I've tested Access97 and '2000 (any office XP'ers care to comment?)  
2)MS had both noticed and admitted it and
3)had MS tried to fix it and I missed a patch?

I have seen MANY clever ways to get around the difficulty, and for that, thanks to everyone! :OD

So, technically speaking, my question as asked (not to mention the 500 points) is still up for grabs.

;O}

Thank you for making it clear; a perception problem on my part, not a software problem.

Wait a minute though. According to the Access help file:

(edited for brevity)
The Jet database engine uses the VBA expression service to perform simple arithmetic and function evaluation. All of the operators used in Jet SQL expressions (except Between, In, and Like) are defined by the VBA expression service. In addition, the VBA expression service offers over 100 VBA functions that you can use in SQL expressions. For example, ...

Given this I would fully expect "12 And 4" to equal 4 and not -1.

Where this really bites you is when you are trying to do bitwise operations in side a RepliaFilter property for a table (Jet replication). In the ReplicaFilter property user defined functions not supported! Therefore, you are forced to using the 2^x division thing blakeh1 suggested.

I WOULD consider it a bug that the VBA expression service is not used in ALL cases, even in a SQL expression.

QuickDraw6906:

Interesting comment, but if you look at the "Specifics" page of that on-line hepl page you will see that it says that "Functions that can be included in SQL strings must return a value that is either a string or a Variant."...

The implication is that SQL will only work with strings.

As you pointed out, 12 AND 4 returns True in a query, which shows that Jet is only doing the logical AND, 12 > 0 and 4 > 0 <=> True AND True <=> True.

The two expressions either side of AND must either both be numeric, or must both evaluate to true or false. In VBA "12" AND "4" evaluates to the nuerical value 4 because VBA implicitly converts the two strings to numbers first.

However SQL, incuding the JET Dialect, first converts the two expressions to boolean values. So 12 AND 4 returns True as any non-zero value is "True"

I agree with everything you said. But I must nitpick a little here. Look at the following statement from the help file:
"All of the operators used in Jet SQL expressions (except Between, In, and Like) are defined by the VBA expression service."

I would take the "All" to mean ALL in this sentence (including the logical/bitwise operators). At issue here is the fact that there are not separate bitwise operators in VB. The problem is that Microsoft function overloaded the token "And" to be used for logical and bitwise operations in VB (VBA).

So, I would expect, when 12 And 4 is seen (or anything like it) that the engine determine if the operands themselves evaluate down to non-evaluatable/irreducible numerals and if so, do bitwise evaluation instead of logical evaluation. Make the user put quotes around numeral operands if they want logical evaluation (or some other scheme to delineate between logical and bitwise evaluation for those function overloaded operators).

There is still one other problem and that's with the Eval() function.

If you run the Query "SELECT Eval(12 And 4) FROM anytable" you get -1 (true). If you have a commandbutton run the following: "Msgbox Eval(12 And 4)" you get 4. Wouldn't you look at this inconsistency as a bug?

Technically the Eval() function is a method of the Application object so one could argue that Jet engine should use it's own evaluation precedents rules to evaluate the expression but I think most people would expect that in a SQL string the Eval() function would be handled the same way that the VBA service does.

Really what I want (for my current project) is a way to do bitwise operations inside the ReplicaFilter property (which doesn't allow user defined functions). If the Eval() function used the VBA service I'd be set. Alas! I guess I'll just do the 2^x math.