Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

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.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

598 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