how to do a boolean "and" in ms access query 1

[spookycave]

I have a numeric (long int) field called RIGHTS in a table called ACL, and I'm running this query on it:

SELECT ACL.* FROM ACL WHERE CBool(RIGHTS And 4)=True;

I just want to select all values that are true, when logically "and'ed" together with the number 4. So I'd want to select values 4, 5, 6, 7, 12, etc. Basically i want a binary "and" operator, but ms access doesn't seem to support it. If I show the value for CBool(RIGHTS And 4), all fields come back as being -1, even when the field value is a 1 or a 2.

I'm running MS Access 2002 sp3. Any help would be much appreciated.
Thanks in advance,
mike

 

[lespaul]

[tt]Question: How do I convert a value to a boolean?

Answer: To convert a value to a boolean, use the "CBool" function. The syntax for the CBool function is:

CBool( expression )

For example:

Dim LCompare as Boolean

LCompare = CBool(1=2)

In this example CBool(1=2) would return false. The variable LCompare would now contain the value false. (

http://www.techonthenet.com/access/functions/datatype/cbool.php)

[/tt]

How is using the CBool going to do what you want?

 

[spookycave]

Thanks for the reply, but I want to do a boolean (bitwise) "AND" between the "RIGHTS" field and the number 4.

For example, in boolean logic:

4 AND 1 = false
4 AND 2 = false
4 AND 4 = true
4 AND 5 = true
4 and 6 = true
etc

or looking at it in binary:

0100 (4)
AND 0001 (1)
------------
0000 (false)

0100 (4)
AND 0010 (2)
------------
0000 (false)

0100 (4)
AND 0100 (4)
------------
0100 (true)

0100 (4)
AND 0101 (5)
------------
0100 (true)

0100 (4)
AND 0110 (6)
------------
0100 (true)

 

[Golom]

Access treats "AND" in an SQL statement as a logical AND ... not as a bitwise operation ... so

RIGHTS And 4

is equivalent to

If RIGHTS <> 0 AND 4 <> 0

which will obviously return FALSE only when RIGHTS is zero.

You may need a function

Public Function BitAnd(RIGHTS as Long) As boolean
   BitAnd = RIGHTS And 4
End Function

SELECT ACL.* FROM ACL WHERE BitAnd(RIGHTS)=True;

 

[spookycave]

That was it. Thanks very much - you saved me from pulling out what little hair I have left. Now to see if I can use the bitand function from VB with an odbc data source.
mike