A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Bitwise And



 
 
Thread Tools Display Modes
  #1  
Old October 2nd, 2004, 11:12 PM
Sam Hobbs
external usenet poster
 
Posts: n/a
Default Bitwise And

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.



  #2  
Old October 2nd, 2004, 11:59 PM
Tom Ellison
external usenet poster
 
Posts: n/a
Default

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


On Sat, 2 Oct 2004 15:12:45 -0700, "Sam Hobbs"
wrote:

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.



  #3  
Old October 3rd, 2004, 03:29 AM
Sam Hobbs
external usenet poster
 
Posts: n/a
Default

"Tom Ellison" wrote in message
news
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...b;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;


  #4  
Old October 3rd, 2004, 04:29 AM
Tom Ellison
external usenet poster
 
Posts: n/a
Default

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


On Sat, 2 Oct 2004 19:29:00 -0700, "Sam Hobbs"
wrote:

"Tom Ellison" wrote in message
news
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...b;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;


  #5  
Old October 3rd, 2004, 02:51 PM
Van T. Dinh
external usenet poster
 
Posts: n/a
Default

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.

--
HTH
Van T. Dinh
MVP (Access)


"Sam Hobbs" wrote in message
...
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.





  #6  
Old October 3rd, 2004, 03:53 PM
Sam Hobbs
external usenet poster
 
Posts: n/a
Default

"Tom Ellison" wrote in message
...
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.


  #7  
Old October 3rd, 2004, 05:45 PM
Sam Hobbs
external usenet poster
 
Posts: n/a
Default

"Van T. Dinh" wrote in message
...
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.


  #8  
Old October 3rd, 2004, 05:47 PM
Sam Hobbs
external usenet poster
 
Posts: n/a
Default

"Tom Ellison" wrote in message
...
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.


  #9  
Old October 3rd, 2004, 08:01 PM
Tom Ellison
external usenet poster
 
Posts: n/a
Default

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


On Sun, 3 Oct 2004 23:51:52 +1000, "Van T. Dinh"
wrote:

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.


  #10  
Old October 3rd, 2004, 10:41 PM
Sam Hobbs
external usenet poster
 
Posts: n/a
Default

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.



"Tom Ellison" wrote in message
...
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.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
bitwise XOR calc Luke Worksheet Functions 1 September 1st, 2004 11:31 AM


All times are GMT +1. The time now is 04:16 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.