48

I'm looking at linked data in MS Access.

The "Yes/No" fields contain the value -1 for YES and 0 for NO. Can someone explain why such a counter-intuitive value is used for "Yes"? (Obviously, it should be 1 and 0)

I imagine there must be a good reason, and I would like to know it.

HansUp
  • 95,961
  • 11
  • 77
  • 135
supermitch
  • 2,062
  • 4
  • 22
  • 28
  • possible duplicate of [Casting a boolean to an integer returns -1 for true?](http://stackoverflow.com/questions/3621037/casting-a-boolean-to-an-integer-returns-1-for-true) – dsolimano Jan 11 '12 at 22:16
  • 15
    Boolean constant True has numeric value −1. This is because the Boolean data type is stored as a 16-bit signed integer. In this construct −1 evaluates to 16 binary 1s (the Boolean value True), and 0 as 16 0s (the Boolean value False). This is apparent when performing a Not operation on a 16 bit signed integer value 0 which will return the integer value −1, in other words True = Not False. This functionality becomes especially useful when performing logical operations on the individual bits of an integer such as And, Or, Xor and Not.[7] This definition of True is also consistent with BASIC ... – Martin Smith Jan 11 '12 at 22:17
  • (From Wikipedia article here http://en.wikipedia.org/wiki/Visual_Basic) – Martin Smith Jan 11 '12 at 22:18
  • Why do you think that -1 is a counter-intuitive value for "Yes"? And why 1 is (obviously) intuitive? – ypercubeᵀᴹ Jan 11 '12 at 22:20
  • @MartinSmith: Convert you comment to an answer. I might be accepted. – Olivier Jacot-Descombes Jan 11 '12 at 22:21
  • 2
    @OlivierJacot-Descombes - It's cut and pasted straight from Wikipedia! – Martin Smith Jan 11 '12 at 22:22
  • 2
    @MartinSmith - Even so, It still answer perfectly this question – Lamak Jan 11 '12 at 22:24
  • 1
    @MartinSmith: note the OP is referring to the MS Access' `YESNO` data type rather than the VBA intrinsic Boolean type. `YESNO` is not the same as Boolean because it can also be the null value (e.g. when using an outer join) i.e. three-valued logic, which has not been defined by the Access team. – onedaywhen Jan 12 '12 at 09:01
  • @dsolimano: this is not an exact duplicate as you suggest, for the reasons I stated to Martin above. – onedaywhen Jan 12 '12 at 09:05
  • @ypercube : Two reasons: -1 is more typing than 1, and "negative" values imply "negation" which to me implies "falsehood". I think 1 and 0, as I've seen in other places, is more obvious. Which isn't to say it's better, just more intuitive. – supermitch Jan 12 '12 at 16:45
  • 1
    I first came across -1 and 0 as true and false back in the 8-bit home microcomputer days, for example in [BBC BASIC](http://www.bbcbasic.co.uk/bbcbasic/manual/bbckey2.html). I think this was when I first saw the trick of -1 and 0 being used because they were bitwise opposites... – Matt Gibson Dec 14 '15 at 20:07

2 Answers2

52

The binary representation of False is 0000000000000000 (how many bits are used depends on the implementation). If you perform a binary NOT operation on it, it will be changed to 1111111111111111, i.e. True, but this is the binary representation of the signed integer -1.

A bit of 1 at the most significant position signals a negative number for signed numbers. Changing the sign of a number happens by inverting all the bits and adding 1. This is called the Two's complement.

Let us change the sign of 1111111111111111. First invert; we get: 0000000000000000

Then add one: 0000000000000001, this is 1.

This is the proof that 1111111111111111 was the binary representation of -1.

UPDATE

Also, when comparing these values do not compare

x = -1

or

x = 1

instead, do compare

x <> 0

this always gives the correct result, independently of the convention used. Most implementations treat any value unequal zero as True.

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
  • 4
    I also would like to give an advice for handlings these -1s. In Some dbs true is 1 in others -1. Instead of comparing `x = -1` or `x = 1`, do compare `x <> 0`, this always gives the correct result. – Olivier Jacot-Descombes Jan 12 '12 at 15:03
  • 3
    Also note that the same things happens in C (and most other languages) where a value of 0 is interpreted as false and all other values are interpreted as true. – Mathieu Pagé Jan 17 '12 at 13:08
-5

"Yes" is -1 because it isn't anything else.

When dealing with Microsoft products, especially one as old as Access, don't assume that there is a good reason for any design choice.

cdeszaq
  • 30,869
  • 25
  • 117
  • 173
  • 4
    This actually **is** a good design choice. You're just looking at it wrong. In Access `False` and `No` are `0`; `True` and `Yes` are defined as not `False`. You can actually use any numeric value to be `Yes`. – Yuck Jan 11 '12 at 22:20
  • Well, though I agree that it may have not been a design choice, there is a reason for this, as explained in @MartinSmith 's comment – Lamak Jan 11 '12 at 22:21
  • @Lamak - In theory, my answer is saying the same thing as the mathematical reason given by Martin's comment above. For a boolean field, the only _other_ option is true, therefore false is the same as "not anything else". Same result as the math version, but a much more direct (and less well supported) way of getting there. – cdeszaq Jan 11 '12 at 22:25
  • @cdeszaq Your answer suggests that the choice of -1 was arbitrary, i.e. any non-zero value would work; that's not entirely true, while non-zero value are generally interpreted as true, _only_ -1 (11...11) is the bitwise NOT of 0 (00...00), for example the bitwise NOT of 1 (00...01) is not 0, it's a big negative number (11...10). – jonvw Aug 04 '17 at 18:04