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

Posted on 2002-05-03
Last Modified: 2012-08-13
In Access97 and Access2000, the "AND" operator will work logically but not bitwise on numeric types if used in the data source property of the field on a report or in an eval() function either in a data source property or in a function.  Have not fully explored forms and queries in this regard yet.  The only instance that I can find where "AND" works as advertised with numeric types is in a function.  

Has anyone else encountered this?  Has anyone found a Microsoft acknowledgement?  How about a patch?  
Question by:keyboards
  • 3
  • 2
  • 2
  • +1

Expert Comment

ID: 6988854
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)

LVL 11

Expert Comment

ID: 6988919
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.

Author Comment

ID: 6992141
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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 11

Accepted Solution

LambertHeenan earned 500 total points
ID: 6992853

"In pure VB it works as advertised" - that too is it, exactly. When you use "AND" in a control's datasource property on a form or report, or in an expression in a query, you are not running any VB code. It is SQL that you are running and in SQL the logical operators only work in a logical manner and not in a bitwize one. So "AND" etc.  works as it is supposed to, and differently, within the the contexts of VB and SQL

Regarding your three queries

1) I'd never really noticed before because I don't often need a bitwize operation on the data in my databases.
2)MS has nothing to admit
3)No patch is called for

It's just like when you write some code in Pascal (or Delphi) and need to call a library routine that is written in C. The function calling conventions differ and you need to be aware of them.

So if you need a bitwize operation within SQL you need to call out to a VB function. Which is what you have already written.


Author Comment

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

Expert Comment

ID: 7273852
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.

LVL 11

Expert Comment

ID: 7277594

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"

Expert Comment

ID: 7278113
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.

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question