Why is MS access boolean true -1 rather than 1 or true?

Question

When I run a query in MS Access I can happily use a query like this:

SELECT clients.* FROM clients WHERE active=True;

or

SELECT clients.* FROM clients WHERE active=-1;

but not

SELECT clients.* FROM clients WHERE active=1;

In addition, say I want to query the database using PDO I might use a prepared statement:

$db->prepare('SELECT clients.* FROM clients WHERE active=:isactive;');
$db->bindValue(':isactive', True); //Does not work
$db->bindValue(':isactive', 1); //Does not work
$db->bindValue(':isactive', -1); //Does work

So even though true works when sending a plain query to Access, if binding only -1 or 0 will work for boolean.

Why is this and why is -1 representative of true when 1 usually means true in other languages/databases?

Answer 1

I can't seem to find the exact source this is from, but I remember reading about this a while ago on I think MSDN. This answer has a technical description of Visual Basic's boolean true, which applies to Access as well.

If I remember correctly, it's because -1 is represented in binary with every bit set to 1 (1111 1111), while +1 only has the least significant bit set to 1 with all the rest 0 (0000 0001). Because false is represented as 0 (0000 0000), it's very easy to change between true and false using a bitwise NOT, but if true was anything else, a bitwise NOT would result in something that isn't false. Also, using bitwise AND to check for truth on any truthy value would work, while if true was 0000 0001 it would not.