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. |
|
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
"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. |
#6
|
|||
|
|||
"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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
"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. |
#9
|
|||
|
|||
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" wrote in message ... "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. |
#10
|
|||
|
|||
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" wrote in message ... "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)! |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
bitwise XOR calc | Luke | Worksheet Functions | 1 | September 1st, 2004 11:31 AM |