Improve company productivity with a Business Account.Sign Up


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

Posted on 2002-05-03
Medium Priority
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.

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

LVL 11

Accepted Solution

LambertHeenan earned 2000 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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

595 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