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.

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

805 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