Detecting if a bit = 0 or 1 in a query (using MOD)

[the_net_2.0]

All,

Here is a where clause that you can use in an access query to determine if any given bit in a value is turned on:

WHERE ((((TABLE.FIELD Mod 128)\64) = 1) = True)

That code will return records where bit #7 in ''FIELD'' is turned on. I poked around with this, and it seems as though this only works for values that represent one bit only. For example, values like these:

• 128
• 256
• 512
• 16
• etc...

Those numbers only have one "1" in the bit string equivalents. The rest are 0's. But if I use any of the following numbers (and their halve values as the divisor) in the where clause, I get erratic results. e.g. - I can't seem to count on them:

• 129
• 257
• 536
• 19
• etc...

There doesn't seem to be a pattern for return values that I can use to substitute for the lack of the NOT bitwise operator in Access's query system.

Can someone offer me a little mathematical explanation of why this works (e.g. - always dependable) for a single bit, but not for multiple bits?

That would be great. Thanks!

 

[Banana]

It's probably because you're trying to use an operator for bitwise comparison when it in fact has nothing to do with it. It's a happy accident that it works for some numbers in very much same way that 2 + 2 and 2 * 2 happens to have same answer, 4 but no other numbers that's not zero or one has this coincidence. You can't build a proof or theory based on such coincidence.

I'd think that you may want to rethink your table design because you shouldn't be using bitmasks as part of your SQL querying. It is very common in coding, yes, but not so in SQL querying. Consider converting that bitmask into a foreign key to a junction table, or design in such way that you don't need to use filters + bitmask. (e.g., bitmask is maybe OK when it's just a column that stores several flags and is never a part of WHERE clause and business rules happen to always requiring viewing it so there is no performance gains to be had from normalizing).

 

[the_net_2.0]

It IS one column though, Banana.

We have a column that begins like this for a value:

2147483647

and the purpose the column exists is because we have 32 different types of engineering products (represented by the 32 bits in that maximum LONG number value), and the program reads that column to see what products are applicable to any given customer when the program is run.

I think that's the strangest thing, but the people I work with are engineers so that's the way they think I guess. 99% of the time though, this column in manipulated by the .edit method using recordsets, and the code is basically this:

variable = rs!bitField and (Not "InputBitsVariable")

Some of our datasets have hundreds of thousands of records in them, and I really don't want to query records for certain products that are not applicable anyway (because the long value already represents 0's for those product types). And the reason I don't is because recordset looping for even 10 datasets when each set has hundreds of thousands of records can take a little time. Querying it out can get it going a tad bit faster, plus the future bugs and inconsistencies will be easier to find.

Does that make more sense? Now you probably see why I want to query them out using bitwise or an equivalency expression. And please, PLEASE, don't lecture me on how inefficient this stuff is. I know. By the way, we are using vb6, if that makes any difference.

 

[ChrisO]

Public Function TestBitN(ByVal lngValue As Long, _
                         ByVal bytBit As Byte) As Integer

    [color=green]' Valid Bit numbers are 0 to 31.[/color]
    If bytBit > 31 Then
        TestBitN = 0
    Else
        If bytBit = 31 Then
            If lngValue < 0 Then TestBitN = True
        Else
            TestBitN = CBool(lngValue And (2 ^ bytBit))
        End If
    End If
    
End Function


Sub Test1()
    Dim X As Long
    
    X = 128
    MsgBox TestBitN(X, 7)
    
    X = 129
    MsgBox TestBitN(X, 7)
    
    X = -1
    MsgBox TestBitN(X, 31)
    
    X = -1
    MsgBox TestBitN(X, 32)
    
End Sub


Sub Test2()
    Dim strSQL As String
    
    Const BitToTest As Byte = 7
    
    strSQL = " SELECT *" & _
             " FROM [Table]" & _
             " WHERE TestBitN([Field], " & BitToTest & ") = -1"

    With CurrentDb.OpenRecordset(strSQL)
        Do Until .EOF
            MsgBox ![Field]
            .MoveNext
        Loop
    End With
    
End Sub

 

[the_net_2.0]

Chris,

Thanks for your answer, sir. I really do appreciate it, but I've got plenty of functions already. Chip Pearson even has a page that has some glorious bitwise functions and subs in it.

I have written my own tests for this too (a simple one-liner will actually do for my purposes), but the entire point of my thread is to NOT call a function.

Sorry about that! Again, thanks.

 

[MarkK]

I think that in ADO you can use BAND, BOR, BXOR, and BNOT as bitwise operators in a where clause.

 

[ChrisO]

Adam.

Can you post a link to the Chip Pearson functions to which you refer?

Chris.

 

[the_net_2.0]

I think that in ADO you can use BAND, BOR, BXOR, and BNOT as bitwise operators in a where clause.

Yes you can, lag. But I don't have the authority at work to include another DLL that's necessary. Right now everything uses DAO in vb6. And making the change for this "desire" of mine is not going to fly. The reason is because it's really not required for me to implement the change I am working on.

And Chris, here's the link you want:

http://www.cpearson.com/excel/BitsAndBytes.aspx

 

[ChrisO]

Thanks Adam, I’ll have a look.

Chris.

 

[gemma-the-husky]

I trhink I don't quite understand how you are trying to use the masks that are not powers of 2 - eg the values of 129, 257 you mention

however, why do you need the /64 in the expression. Given a single byte, why not just use AND

ie instead of your example
WHERE ((((TABLE.FIELD Mod 128)\64) = 1) = True)

just

WHERE (TABLE.FIELD AND 64) = 64 - will determine whether the 64 bit is set.

or
WHERE (TABLE.FIELD AND 129) <> 0 would detect whether either the 128 bit, or the 1 bit were set.

not sure of the syntax without checking, but you can "get at" a single byte within a longer size variable easily enough, I think.

 

[Banana]

Gemma -

SQL's "AND" operator is logical and there's no bitwise operator in SQL. It may work in VBA since VBA's "And" operator is bitwise and by extension could be also used as logical operation with caveats.

In SQL, though a "AND" would only give you "True" (aka -1) for the expression "128 AND 64" which isn't equal to 64 and therefore is false.

Furthermore, "8 AND 4" = -1 but do it in VBA and "8 And 4" = 0. Both are correct as long one is mindful of whether the And operator is a logical or bitwise operator and it's being used accordingly.

 

[Galaxiom]

The original formula you posted is correct for testing the seventh bit (64). It isn't completely clear from your description but I suspect you are misunderstanding the binary values of the numbers you are testing.

The Mod divisor should be twice the value of the bit you are testing.

 

[the_net_2.0]

The Mod divisor should be twice the value of the bit you are testing.

It IS. I don't understand. The MOD divisor is 128 in my original post, and I'm testing bit 7, which is 64 for a value. That's just what you said, no?

But the issue is checking more than one bit with this process. Actually I don't care about checking all bits in a value. All I want to know is if ONE bit in a group of bits is turned on. For instance, if I have an input of "3", which is "11" in binary, I want to check to see if bit 1 OR 2 is on in the bit string I already have. If either one is on, the query must run.

There's no mathematical pattern to this, I wonder? As in, deciphering a return value?

 

[Galaxiom]

I don't know why it doesn't work for you.

You can OR test for consecutive bits by using the Mod Divisor twice the highest test bit and the Integer Divide Divisor equal to the lowest test bit. Any result greater than zero indicates at least one of the bits is on.

The result is effectively a bitwise right shift of the selected bit group so it does indicate which bit is on in binary. In a test of two consecutive bits the result of the calculation would be 1 for the LSB , 2 for the MSB and 3 for both bits.

 

[MarkK]

This formula returns a one if <bit> is set in <value>, and zero if not.

(<value>\(2^<bit>)) mod 2

To test for position 1, use a <bit> value of zero, so ...

? (1\(2^0)) mod 2

... returns a 1.

 

[the_net_2.0]

This formula returns a one if <bit> is set in <value>, and zero if not.

(<value>\(2^<bit>)) mod 2

Lag,

Is there a way to modify this to check for more than one bit? And does it accept <bit> in the form of 1,2,3,4,5, (actual bit position)... instead of actual bit ceiling values?

What I mean by bit ceiling values are the values that always give the MSB = 1 and the rest of the bits = 0.

FYI - the combination of "on" bits also have different value representations though. For instance, if I got an input configuration of 192, that translates to 11000000 in binary. So it's not a "bit ceiling" value. Can you modify what you've already got to satisfy that requirement?

thanks for what you came up with. Looks good!

 

[MarkK]

Is there a way to modify this to check for more than one bit?

Well, you can do this check more than one time.

And does it accept <bit> in the form of 1,2,3,4,5, (actual bit position)... instead of actual bit ceiling values?

Yes. The <bit> parameter IS the power of 2, so it's 2^0 for a bit in position 1, 2^1 for a bit in position 2 and so on...

Can you modify what you've already got to satisfy that requirement?

As far as I can see this formula satisfies the stated requirement. Am I missing something?
Cheers,

 

[the_net_2.0]

As far as I can see this formula satisfies the stated requirement. Am I missing something?
Cheers,

Not really. You told me that I could do this check for every bit needed. And of course if a check tells me that a bit is on, I can stop concatenating 'OR' operator statements in the query.

But say I'm given an input of 192 (which is only the 7th and 8th bit turned on). That means that using your expression, I would have to write this as my vb6 criteria for running a query:

if ((192\(2^7)) mod 2) = 1 or ((192\(2^8)) mod 2) = 1 then
.....

But my inputs can be as large as 256. (I have 8 bits that I'm currently working with, but over the years that will expand to more. We have 32 possible bits we can work with...right now we're up to 8.)

So if I need to check all 8 bits to get to the bit that's actually on, that would mean that my code could potentially look this long:

if ((192\(2^8)) mod 2) = 1 or _
((192\(2^7)) mod 2) = 1 or _
((192\(2^6)) mod 2) = 1 or _
((192\(2^5)) mod 2) = 1 or _
((192\(2^4)) mod 2) = 1 or _
((192\(2^3)) mod 2) = 1 or _
((192\(2^2)) mod 2) = 1 then
......

which defeats the purpose of trying to consolidate the task of checking "192" for "bits that are on" in one small operation.

Follow? Can this be consolidated anymore using your expression method? Or am I stuck with a concatenation or 'OR's in a query, using your single expression?

 

[MarkK]

If all bits are off then the number is zero, correct? So to check if some bits, any bits, are on, check that the number <> 0.
Wouldn't that work?
So your code becomes...

[COLOR="Green"]'if ((192\(2^8)) mod 2) = 1 or _
'((192\(2^7)) mod 2) = 1 or _
'((192\(2^6)) mod 2) = 1 or _
'((192\(2^5)) mod 2) = 1 or _
'((192\(2^4)) mod 2) = 1 or _
'((192\(2^3)) mod 2) = 1 or _
'((192\(2^2)) mod 2) = 1 then
'......[/COLOR]
if 192 > 0 then
......

Right? Your if block expression has no better resolution than that.
Maybe the problem resides in how you are conceptualizing what a bitmask can do for you. Each bit can function as a single memory location that may indicate the presence or absence of a single attribute, much like a series of boolean fields, but if you treat it that way you have to check every bit (field). If you treat it that way, it's not one number anymore, it's 30 or so different ones.
Or am I missing something?
Cheers,

 

[the_net_2.0]

If all bits are off then the number is zero, correct? So to check if some bits, any bits, are on, check that the number <> 0.
Wouldn't that work?
So your code becomes...

[COLOR="Green"]'if ((192\(2^8)) mod 2) = 1 or _
'((192\(2^7)) mod 2) = 1 or _
'((192\(2^6)) mod 2) = 1 or _
'((192\(2^5)) mod 2) = 1 or _
'((192\(2^4)) mod 2) = 1 or _
'((192\(2^3)) mod 2) = 1 or _
'((192\(2^2)) mod 2) = 1 then
'......[/COLOR]
if 192 > 0 then
......

Right? Your if block expression has no better resolution than that.
Maybe the problem resides in how you are conceptualizing what a bitmask can do for you. Each bit can function as a single memory location that may indicate the presence or absence of a single attribute, much like a series of boolean fields, but if you treat it that way you have to check every bit (field). If you treat it that way, it's not one number anymore, it's 30 or so different ones.
Or am I missing something?
Cheers,

Yes, to check if any bits are on, you check for <> 0. And that is already one conditional I have, but the problem lies in updating my configuration value to represent the fact that I've shut the bits off.

Maybe this little scenario of how I'm working with this will help:

• I start with a bit configuration value in a field, max value is 2147......(whatever the max is for LONG type).
• I receive an input value from the user, that represents "bit effectivity", in the form of a long integer.
• I want to run a query to update the bit configuration value if ANY of the bits that are ON in the "effectivity" value are actually ON in the bit configuration value - e.g....The ON bits in effectivity represent what bits need to be shut off (in vb6 directly, you can get this result by simply saying [CONFIG VALUE] AND NOT([INPUT VALUE]).
• If the "effectivity" bits are already shut off, I want to bypass the query code.

So the whole point of this thread was to try and nail down the mathematical equivalent to the bitwise expression in bold. MS Access queries can't use bitwise.