Tom Ellison said:
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;