Bitwise And

[Sam Hobbs]

I have the following in a module that works:


Const DB_SYSTEMOBJECT = &H80000002

Public Function IsSystemObject(Flags)
IsSystemObject = (Flags And DB_SYSTEMOBJECT)
End Function


However when I use "Flags And 2147483650" in a query, I don't get the same
results as I do when I say "IsSystemObject(Flags)". Is a way to do the
equivalent of the IsSystemObject Function except in a query directly?

I guess the problem is that the And operator has dual purposes; it is either
a logical or a bitwise operator. So I can probably figure out a way to get
it to be a bitwise operator, but if someone does not mind enlightening me
with their experience, then that will problably help.

Note: Some people will recognize that this is testing the Flags field of the
MSysObjects table to determine if the object is a system object.

 

[Tom Ellison]

Dear Sam:

In VBA, the values are probably being cast as long integers. As a
long integer, the value of &H80000002 is not 2147483650, but it is
-2147483646. The highest order bit is a sign of the number, making
the value 2 act as a 2's complement over the 31 bits. I think it may
be that, if you use this value in a query, it will match your function
results.

To see this, use:

? DB_SYSTEMOBJECT

in the immediate pane and see what value is shown.

I also recommend you do more typing of variables and constants in the
function, and of the function itself:

Const DB_SYSTEMOBJECT As Long = &H80000002

Public Function IsSystemObject(Flags As Long) As Boolean
IsSystemObject = CBool((Flags And DB_SYSTEMOBJECT) = DB_SYSTEMOBJECT)
End Function

I have also converted the results to be Boolean, as is the type I
assigned to the function itself. Functions beginning with "Is" are
typically boolean, not returning the bits resulting from the masking.

Is the object a system object if either of the masked bits are 1 or
only if both are? I've altered the function to mean both must be, not
knowing for sure whether this is what is wanted.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Sam Hobbs]

Dear Sam:

In VBA, the values are probably being cast as long integers. As a
long integer, the value of &H80000002 is not 2147483650, but it is
-2147483646. The highest order bit is a sign of the number, making
the value 2 act as a 2's complement over the 31 bits. I think it may
be that, if you use this value in a query, it will match your function
results.

To see this, use:

? DB_SYSTEMOBJECT

in the immediate pane and see what value is shown.

I did try to ensure I got the correct conversion from hexadecimal to
decimal. However earlier today I used the Power Toy calculator to do the
conversion, and I verified it using the Windows calculator. They sometimes
do not correctly convert negative numbers or numbers with the left-most bit
on. So I put the following in a quicky test:

Dim MinLong, MaxLong As Long
MinLong = -2147483648#
MaxLong = 2147483647
Debug.Print Hex(MinLong), Hex(MaxLong), Hex(-2147483646)

And got:

80000000 7FFFFFFF 80000002

So you are right, I should be using -2147483646. However I probably did use
Debug.Print in a similar manner when I tried to solve the problem myself a
few days ago and I probably did use -2147483646 then. I get the same results
in the query when I do use -2147483646.

The first Flags value that there is a problem with is 1048576 (&H100000).
The following code shows the correct value (0):

Debug.Print DB_SYSTEMOBJECT And &H100000

Whereas in the query I have "Expr1: [Flags] And -2147483646", which results
in -1.

I also recommend you do more typing of variables and constants in the
function, and of the function itself:

Const DB_SYSTEMOBJECT As Long = &H80000002

Public Function IsSystemObject(Flags As Long) As Boolean
IsSystemObject = CBool((Flags And DB_SYSTEMOBJECT) = DB_SYSTEMOBJECT)
End Function

I have also converted the results to be Boolean, as is the type I
assigned to the function itself. Functions beginning with "Is" are
typically boolean, not returning the bits resulting from the masking.

Most of my recent programming has been using C++, and I would prefer that VB
be as thorough about types as C++ is. I get confused about what version of
VB (scripting or not) in which I can ensure that the correct types are used,
so until I have more experience with VB, I have been lazy about specifying
types. It is (mildly) frustrating for me when I specify a type and VB
doesn't understand.

As for the "CBool((Flags And DB_SYSTEMOBJECT) = DB_SYSTEMOBJECT)" part, I
made my code similar to the code in the documentation as is in:

108148 - How to Delete a Field from a Populated Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;108148

Specifying a return type is certainly a good idea, especially if code will
be used by others, and certainly in the case of a function prefixed by "Is".

However note my question si how to eliminate the "IsSystemObject" function.
All of the improvements to the "IsSystemObject" function are not needed if I
can eliminate the need for it.

Is the object a system object if either of the masked bits are 1 or
only if both are? I've altered the function to mean both must be, not
knowing for sure whether this is what is wanted.

A system object has one of the two bits on. I have not seen a flag value of
&H80000002. So now I remember using code similar to what you suggest above,
and then I realized that we need to test for only one bit being on.

So please understand that I have already put a lot of time into this, and I
do understand the technical details. I am being a little lazy but I also
might have overlooked something relatively simple. I apprecieate your help
ensuring I have not overlooked something.

So the following shows SQL you can use to see what I am talking about. If
you use this in a database with a linked table, then the Flags will probably
be 1048576 (&H100000), as I mention above.

SELECT MSysObjects.Name, Hex(MSysObjects!Flags) AS Flags,
IsSystemObject([Flags]), MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Flags)<>0))
ORDER BY MSysObjects.Name;

 

[Tom Ellison]

Dear Sam:

If you need to test for either bit:

Const DB_SYSTEMOBJECT As Long = &H80000002

Public Function IsSystemObject(Flags As Long) As Boolean
IsSystemObject = CBool(Flags And DB_SYSTEMOBJECT)
End Function

I don't know that there would be much advantage to going without the
function and doing the calculation in the query. A function like the
above is not going to be especially slow. Does the query work
correctly now with the changed value for the mask?

I tired to include everything I could find earlier. The "guess" that
it might to test for "both bits" instead of either was just a shot in
the dark, but I figured that by adding this note it just might help.
If not needed, it's not much work to this change I suggested.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear Sam:

In VBA, the values are probably being cast as long integers. As a
long integer, the value of &H80000002 is not 2147483650, but it is
-2147483646. The highest order bit is a sign of the number, making
the value 2 act as a 2's complement over the 31 bits. I think it may
be that, if you use this value in a query, it will match your function
results.

To see this, use:

? DB_SYSTEMOBJECT

in the immediate pane and see what value is shown.

I did try to ensure I got the correct conversion from hexadecimal to
decimal. However earlier today I used the Power Toy calculator to do the
conversion, and I verified it using the Windows calculator. They sometimes
do not correctly convert negative numbers or numbers with the left-most bit
on. So I put the following in a quicky test:

Dim MinLong, MaxLong As Long
MinLong = -2147483648#
MaxLong = 2147483647
Debug.Print Hex(MinLong), Hex(MaxLong), Hex(-2147483646)

And got:

80000000 7FFFFFFF 80000002

So you are right, I should be using -2147483646. However I probably did use
Debug.Print in a similar manner when I tried to solve the problem myself a
few days ago and I probably did use -2147483646 then. I get the same results
in the query when I do use -2147483646.

The first Flags value that there is a problem with is 1048576 (&H100000).
The following code shows the correct value (0):

Debug.Print DB_SYSTEMOBJECT And &H100000

Whereas in the query I have "Expr1: [Flags] And -2147483646", which results
in -1.

I also recommend you do more typing of variables and constants in the
function, and of the function itself:

Const DB_SYSTEMOBJECT As Long = &H80000002

Public Function IsSystemObject(Flags As Long) As Boolean
IsSystemObject = CBool((Flags And DB_SYSTEMOBJECT) = DB_SYSTEMOBJECT)
End Function

I have also converted the results to be Boolean, as is the type I
assigned to the function itself. Functions beginning with "Is" are
typically boolean, not returning the bits resulting from the masking.

Most of my recent programming has been using C++, and I would prefer that VB
be as thorough about types as C++ is. I get confused about what version of
VB (scripting or not) in which I can ensure that the correct types are used,
so until I have more experience with VB, I have been lazy about specifying
types. It is (mildly) frustrating for me when I specify a type and VB
doesn't understand.

As for the "CBool((Flags And DB_SYSTEMOBJECT) = DB_SYSTEMOBJECT)" part, I
made my code similar to the code in the documentation as is in:

108148 - How to Delete a Field from a Populated Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;108148

Specifying a return type is certainly a good idea, especially if code will
be used by others, and certainly in the case of a function prefixed by "Is".

However note my question si how to eliminate the "IsSystemObject" function.
All of the improvements to the "IsSystemObject" function are not needed if I
can eliminate the need for it.

Is the object a system object if either of the masked bits are 1 or
only if both are? I've altered the function to mean both must be, not
knowing for sure whether this is what is wanted.

A system object has one of the two bits on. I have not seen a flag value of
&H80000002. So now I remember using code similar to what you suggest above,
and then I realized that we need to test for only one bit being on.

So please understand that I have already put a lot of time into this, and I
do understand the technical details. I am being a little lazy but I also
might have overlooked something relatively simple. I apprecieate your help
ensuring I have not overlooked something.

So the following shows SQL you can use to see what I am talking about. If
you use this in a database with a linked table, then the Flags will probably
be 1048576 (&H100000), as I mention above.

SELECT MSysObjects.Name, Hex(MSysObjects!Flags) AS Flags,
IsSystemObject([Flags]), MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Flags)<>0))
ORDER BY MSysObjects.Name;

 

[Van T. Dinh]

In JET, I am fairly sure the "AND" is a purely logical operator. "BAND" is a
bitwise AND operator introduced in JET 4 but there may be problems using it
in the Query interface. I am fairly sure BAND works in ADO code but you
will have to try it out.

Note that in your result, -1 means True but this doesn't mean that it is a
SystemObject since in Query, and expression like 1 AND 2 will returns -1.
The reason is that any non-zero number is interpreted as True and only 0 is
interprested as False. So the above expression will be imterpreted as True
AND True which results in True, i.e. -1.

 

[Sam Hobbs]

Dear Sam:

If you need to test for either bit:

Const DB_SYSTEMOBJECT As Long = &H80000002

Public Function IsSystemObject(Flags As Long) As Boolean
IsSystemObject = CBool(Flags And DB_SYSTEMOBJECT)
End Function

Thank you. That is a better version of IsSystemObject I will use if I use
the function.

I don't know that there would be much advantage to going without the
function and doing the calculation in the query. A function like the
above is not going to be especially slow.

Yes, there is not much advantage. If the IsSystemObject function or similar
code is needed, then it is not a problem. I just wanted to simplify things
if possible, but it is not an important thing to do.

Does the query work
correctly now with the changed value for the mask?

No. I am sorry the wrong mask value confused things; I think everything else
would have been the same if I had provided the correct one.

Thank you for the help you have provided.

 

[Sam Hobbs]

In JET, I am fairly sure the "AND" is a purely logical operator. "BAND" is
a
bitwise AND operator introduced in JET 4 but there may be problems using
it
in the Query interface. I am fairly sure BAND works in ADO code but you
will have to try it out.

Access says that BAnd is not valid as an operator and I can't find BAnd in
the list of functions for queries. However it probably helps a lot for me to
realize that I should be able to use whatever works in SQL, so that is what
I should look for. I should be able to get an answer relatively easily by
looking in forums and such for the SQL way to do this.

Note that in your result, -1 means True but this doesn't mean that it is a
SystemObject since in Query, and expression like 1 AND 2 will returns -1.
The reason is that any non-zero number is interpreted as True and only 0
is
interprested as False. So the above expression will be imterpreted as
True
AND True which results in True, i.e. -1.

Yes, I understand. That is how C/C++ works; at least, for the C/C++ logical
(boolean) operator "&&", 0 is false and anything that is not 0 is true.

 

[Sam Hobbs]

Dear Sam:

If you need to test for either bit:

Const DB_SYSTEMOBJECT As Long = &H80000002

Public Function IsSystemObject(Flags As Long) As Boolean
IsSystemObject = CBool(Flags And DB_SYSTEMOBJECT)
End Function

Again thank you. You are probably correct that the extra specifications of
data types is necessary. I think I am finding erroneous results when I use
my version without the extra specifications of data types.

 

[Tom Ellison]

Dear Van:

I had to check out my facts and also test the function thoroughly
before I began responding here.

From online help:

The And operator also performs a bitwise comparison of identically
positioned bits in two numeric expressions and sets the corresponding
bit in result

This is the case for VBA, and for the use of And in a function there.
It tests out correctly there.

Perhaps you were referring to the use of And in a query. Could its
use be different there? I wouldn't think so, but haven't tested it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Sam Hobbs]

Use of "And" in a query is different. So the following should be a simple
reproduction of that.

First, the following is a table; two columns separated by "|" (vertical
bar). The Flags are of course an "Integer Long" field.

-------------------------------- Begin table
Flags|Comments
-2147483648|System Object
2|System Object
3|System Object and Unique Relation?
1048576|Non-system Object; Unknown flag
-------------------------------- End table

Then the following is a query for that table. The IsSystemObject function is
provided elsewhere of course.

SELECT Hex([Flags]) AS HexFlags, FlagsTest.Comments, IsSystemObject([Flags])
AS UsingCode, [Flags] And CLng(-2147483646) AS NotUsingCode
FROM FlagsTest;

Using that, the "UsingCode" and "NotUsingCode" values will be different for
1048576.

 

[Van T. Dinh]

Hi Tom

Yes, I referred to "AND" in Queries / SQL. My previous post started with
"In JET, ...".

When you tested the "function" (as or similar to what Sam posted), you
tested the VBA "AND", not the JET "AND".

Test with the following SQL:

SELECT (4 And 4) As Test
FROM Table1

where Table1 is any Table with Records. You should get a whole column of -1
(True, i.e. logical operator) and not 4 (If JET And were bitwise operator).

 

[Van T. Dinh]

I am not sure where I read it from but BAND is definitely in JET 4. Note
that my previous post mentioned that it may not work though the Query
interface or the code using DAO.

I did a quick little test code (using ADO, of course) in A2002:

****Code starts****
Public Sub Test_BAND()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
strSQL = "SELECT (4 BAND 4) As Test FROM Table1"

rs.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly

With rs
While (rs.EOF) = False
Debug.Print rs.Fields("Test").Value
rs.MoveNext
Wend
End With

rs.Close
Set rs = Nothing
Set cnn = Nothing

End Sub
****Code ends****

"Table1" can be any Table with some Records. When tested, it worked
correctly giving me a column of 4 (i.e. bitwise op) in the Debug window. If
I replace "BAND" with "AND" in the SQL String, I get a column of -1 (True,
i.e. logical op)!

In fact, in the SQL String, you don't even need the FROM clause and the SQL
String will give only one row (and only one column).

--
HTH
Van T. Dinh
MVP (Access)

 

[Sam Hobbs]

Thank you.

Yes, the Test_BAND procedure works as you say. However if I copy the SQL and
paste it into an Access query, Access complains.

I found something about SQL Server somewhere that says that BITAND is the
equivalent of the "&" operator, but neither of those work for an Access
query. At least I was not able to get them to work; the "&" operator always
concatenated text values.

Access queries do seem to do their own thing.

 

[Van T. Dinh]

Sam

You are not reading my post fully. I wrote *twice* previously that there
are problems using BAND in the Access Query interface or DAO code!

I think the reason is that Access Query interface was carried over from A97
which was designed for JET 3.5 and BAND was introduced in JET 4. Note that
JET and Access are 2 separate and distinct components (a third major comp is
VBA/Access VBA) and Microsoft would have had to do a fair bit of work to get
them to work together. I number of new elements in JET 4 SQL syntax were
somehow not incorporated into the Access Query interface.

 

[david epsom dot com dot au]

FWIW, when using 'ANSI' mode, you can use 'BAND'
in queries: (2 BAND 4)

(david)

 

[Sam Hobbs]

I am sorry I misunderstood. However I have read the posts again and I still
don't understand.

Originally you said .... "but there may be problems using it in the Query
interface" .... "but you will have to try it out". Then you said "Note that
my previous post mentioned that it may not work though the Query interface
or the code using DAO.".

So I thought you were saying you were not sure so I was reporting my
results. I get frustrated when people don't provide feedback to my messages
when I try to help, so I was just doing for you what I would want others to
do for me.

 

[Van T. Dinh]

OK, just a bit of misunderstanding (on my part) as I didn't expect feedback
of testing with the Query interface.

What I meant in previous posts is that I had problems using BAND through the
Query interface and DAO. However, there are a lot of smart people around
and they may prove that I am wrong and there are ways to use BAND through
the Query interface or DAO (which I haven't found yet) and therefore I wrote
"There may be problems ...). You may even prove me wrong later!

When I wrote "try it out", I meant trying in ADO code and I subsequently
posted ADO code in a later post. Hence, I didn't expect you to test with
the Query interface or DAO code.

 

[Sam Hobbs]

Thank you. That helps; I assume it answers the question of whether BAnd
works in a query.

As for FWIW, I don't know what it is and I can't find it in the Access
documentation or MSDN.

As for ANSI SQL query mode, the documentation says that it applies to an
entire database. If so, then unfortunately it is not useful for this. This
is a general-purpose solution for use in many databases; that is, it needs
to work with the most common Access databases.

 

[david epsom dot com dot au]

:~) For What It's Worth

In your case, worth only a penny I guess :~)

Note: it is possible, using ADO, to create
ANSI queries in a Non-Ansi database. In Access
2000 it is not possible to put the database
into ANSI mode, and any ADO-created ANSI
querydefs are not visible in the Access
database window. But they are still there, and
may be enumerated.

AFAIK (As Far As I Know :~), no-one has ever
discussed the behaviour in Access of ANSI queries
in a (non-ANSI) A2000 database, other than
to say that they are not visible if they exist.

Access/Jet querydefs have two streams: Text
(SQL), and compiled (binary). Both ANSI and
non-ANSI SQL will compile, and the compiled
query is what is used, not the actual Text
stream. So you would expect that compiled
ANSI querydefs might be used interchangeably
with compiled non-ANSI querydefs, but I've
never tried it.

If you create an ADO connection to an A2K database,
and use the connection to create a query, you
apparently get an ANSI query. I know this because
there used to be questions here like: "I used
ADO to create a query in my database. It's there,
but I can't see it in the Access database window.
Why not?"

(david)

 

[Sam Hobbs]

Oh, I should have recognized "FWIW" but I did not. My mind was sure blank
but now it seems very obvious.

Thank you for this too. I don't know how to create queries using ADO. I
assume I can figure that out and I will eventually. Perhaps the meaning of
what you are saying is also obvious and I will realize it later. It does
sound interesting and useful. I am certainly interested in knowing what an
ANSI query looks like in the MSysObjects table. When you say "create an ADO
connection to an A2K database, and use the connection to create a query", I
assume that creation is not the same thing as executing. I assume you mean
that creation is done once and then the query can be executed without ADO,
but I don't know how.