True Comparison Expression returns -1, why?

[ilcaa72]

here is a screenshot.

i created a calculated field which compares 2 column values. if its TRUE then the calculation returns -1 (which is odd) if FALSE it returns 0

is there a way to make True 1 and leave False as 0 . Or whats the reasoning..Thanks

 

[plog]

That's just how programming works. 0 just seems right for being false, which makes not 0 true. Also, 1 is odd as well, so I don't really see how that point relates to anything.

As for converting it, you could use the absolute value, you could multiply the result by -1, you could use an IIf statement, you could divide the result by -1, you could add 2 to the result and take the modulus, you could....

 

[Tieval]

You could convert it to a string and check if the length is greater than one.

anymore offers

 

[Frothingslosh]

If it's just in a table, I wouldn't worry about it. You can always translate it to true/false in reports and forms.

 

[The_Doc_Man]

Using -1 for TRUE is a hold-over from primitive circuitry where that was the easiest way to support the test regardless of the number of bits in the Boolean variable, which has in the past been 1 bit (and "packed Boolean" in ADA is STILL 1 bit), 4 bits (on Intel 4004 systems and a couple of early TI and HP chips), 8 bits (on Intel 8008, 8080, and Z-80). Just because that was the easiest way to do it in circuitry that pre-dates the days of large-scale integration (much less very large, ultra-large, and the nightmares that IBM is making now with billions of components on a chip.)

The reason was mostly because they wanted to keep the circuitry as simple as possible, so that answer was a simple solution.

Why is it like that way now? Because programs are still running that use that principle and nobody wants to bring up the subject of changing the representations of TRUE and FALSE because of the old-line users who would raise six kinds of Hell and would bludgeon you about the head, neck, and shoulders with signs on the subject of "backwards compatibility." Heck, if some of the protesters are pretty enough, I'd carry a sign for them, too!

 

[ilcaa72]

That's just how programming works. 0 just seems right for being false, which makes not 0 true. Also, 1 is odd as well, so I don't really see how that point relates to anything.

As for converting it, you could use the absolute value, you could multiply the result by -1, you could use an IIf statement, you could divide the result by -1, you could add 2 to the result and take the modulus, you could....

the TRUE is showing as a -1 not a 1.

I understand why 1 would be TRUE. 1 is ON, 0 if OFF. But why is Access showing -1 for a TRUE

if this is normal for Access then I will deal with it, but VBA shows a true condition as a positive number, not a negative

 

[plog]

if this is normal for Access then I will deal with it, but VBA shows a true condition as a positive number, not a negative

Incorrect. 0 evaluates to False. Non-0 numeric values evaluate to True.

 

[pbaldy]

why is Access showing -1 for a TRUE

Access stores True as -1:

https://support.office.com/en-us/ar...o-values-4FA55FFF-B3A0-4D03-A7A6-A2CFE4D03D4C


As has been mentioned, you could add the Abs() function to change it to 1.

 

[Galaxiom]

Using -1 for TRUE is a hold-over from primitive circuitry where that was the easiest way to support the test regardless of the number of bits in the Boolean variable,

The deeper reason underlying this is the way all computers have stored binary numbers since the end of the 1960s. It is called Two's Complement.

In this system, negative numbers are essentially stored backwards compared to human readable numbers. Minus one has all the bits (including the sign bit) set to 1. Minus 2 has the least significant bit set to 0 and so on. It simplifies the arithmetical processing of negative and positive numbers together because they can be added bitwise regardless of the sign.

The long deprecated alternative, One's Complement, is like the human readable numbers. It sets the sign bit to 1 for negative and otherwise stores the number exactly as the positive version. Hence, adding negative numbers must be treated as subtraction, exactly as we humans do. It also means there are two representations of zero, (negative and positive zero.), which is not the case in Two's Complement.

So even though True is displayed as minus 1, every bit at the binary level is 1.

In modern practice, all numbers other than zero are converted to True because True is determined as Not 0.

 

[The_Doc_Man]

I understand why 1 would be TRUE. 1 is ON, 0 if OFF. But why is Access showing -1 for a TRUE

OK, let's do this from another perspective. The reason is because you are unknowingly doing something wrong. Access VBA does something called automatic typecast. When you see the -1 for TRUE, the problem is that you see TRUE after it has been typecast by the VBA expression analyzer. That happens because Boolean gets TYPECAST to type BYTE (integer). To see that variable correctly, you need to force it back to its proper type.

debug.print CBool(-1)

will print TRUE. You can experiment with other values as you wish to see what gets printed. What is happening is that Boolean variables or Yes/No fields are both implemented by a typecast of a BYTE integer. So when you go to print them, VBA converts them to their base type.

By the way, this is the same reason that you can add numbers directly to dates. They are typecasts of type DOUBLE, so adding to a DATE gets converted (behind the scenes) to simply adding to a DOUBLE value.

 

[static]

A Boolean is a number. Since you haven't told Access what datatype your calculation returns it uses the most appropriate format.

In the table, under 'Expression' on the General tab, you also have 'Result Type' and 'Format' properties.

Result type should be set to Yes/No.
Format gives you 3 options to choose from.

The field will then show text (making data easier to read (I guess)) but the actual underlying value is still 0 or -1.

 

[The_Doc_Man]

Static, I'm going to split hairs on this one: Technically, an Access Yes/No field or Boolean VBA variable is NOT a number in the conventional sense of the word - it is a logic value - but the way it is stored involves a TYPECAST of something that IS a number. In other words, this is an implementation issue, not an inherent data type issue.

When debug.print or other elements look at the item "raw" they see something that is a TYPECAST of a BYTE integer, which IS a number. In other words, they see the inherited properties of the Boolean variable as stored. But it is stored that way ONLY because there is no smaller storage unit available than a byte.

In a strongly typed language such as ADA, this doesn't occur - but on the other hand, it becomes a pain in the toches to deal with the strongly declared datatype. The way Access does it is easier because BASIC does automatic conversions to the nearest compatible type as a sort of shortcut. Convenient as heck, but it hides some things from you in the process of what it is doing. That is why the confusion exists.

 

[static]

I know what logic is and I know what values are but logic values?
Not sure what your point is.

 

[Galaxiom]

I know what logic is and I know what values are but logic values?
Not sure what your point is.

Logic values are True and False. They are not numbers.

Yes, they are stored as numbers but if you define logic values as being numbers in those terms, then you would also have to say that strings are numbers too.

 

[static]

Strings are numbers.

+
So since everything in a computers brain is binary 0 or 1 everything is numeric. Glad we got that sorted.

A boolean field in an Access table is Yes/No which is 0 or -1 (numeric). As stated here by others.

Since the OP's question is regarding a boolean return type in a table (Yes/No), my response is that he should format it as such using the available table properties for that field.

 

[Galaxiom]

Strings are numbers.

+
So since everything in a computers brain is binary 0 or 1 everything is numeric. Glad we got that sorted.

A boolean field in an Access table is Yes/No which is 0 or -1 (numeric). As stated here by others.

Since the OP's question is regarding a boolean return type in a table (Yes/No), my response is that he should format it as such using the available table properties for that field.

OK, let's get really pedantic. (Doc did say he was splitting hairs.)

In fact there are no numbers in computers at all. There are nothing but arrays of True and False. As a single bit, True is interpreted as 1 and False as 0 in binary notation.

In Access, Boolean True is represented by setting every bit in the byte to True. (Or in the case of 64 bit memory the bits in all four bytes are set to True). You can't get any Truer than that.

It just so happens that all bits True in Two's Complement binary notation represents minus one. True is not stored as minus one.

I will further emphasise this by comparing the bit datatype in SQL Server. In this environment, True is also stored as True in a single bit as part of a byte. A single byte stores up to eight independent bit fields. Hence SQL Server returns 1 for True because it does not represent minus one.

 

[static]

I was trying to be helpful.

Magnetic on/magnetic off.

 

[Galaxiom]

I was trying to be helpful.

@ Doc: Not sure what your point is.

I was explaining Docs point.

Magnetic on/magnetic off.

It isn't that simple. The disc does not hold True and False as field and no field but encodes them as series flux transitions. The disc controller converts between the bytes sent to it by the processor and what is written to the disk.

Logical True and False bits don't actually exist on the disk.

I don't know exactly how the internals of SSDs work so I won't go there.

 

[static]

Ok. By this point I'm far too drunk to argue how hard disks work orhow OS's read/translate their bits.

But even if I wasn't I wouldn't care becuase it makes no difference to the question raised.

In VBA 0 is false. Any other number is true. Yees? Boolean is therfore numeric. Aye?

 

[The_Doc_Man]

Static, you are still missing the point that Greg and I were making. On the other hand, with regarding to getting drunk... want company? But I digress.

In VBA or in any other formal computer language I know, and I can count at least half a dozen major ones, FALSE is false and TRUE is true. Boolean is NOT A NUMBER.

The problem that was being explained is that unfortunately, VBA and Access (and a lot of other languages) DO NOT HAVE a native Boolean variable because they are limited to whole-byte addresses. They do not do bit-mode addressing. It is a language implementation constraint.

Internally there is a representational convention in which a BYTE INTEGER acting as a Boolean variable gets either 0, meaning FALSE, or -1, meaning TRUE. But this is an ARTIFACT of the implementation that occurs because there IS no native data type that is 1 bit long and stores either true or false.

It is the same thing as saying that an "infinite" result is often stored as the maximum value for a given representation. So a signed LONG representation of infinity is +2 billion and change. A SINGLE representation of infinity is 1.something x 10^38. These are representation artifacts. A DOUBLE, if typecast as a DATE, stores any date after the epoch starting reference of 0, representing 31-Dec-1899 (or 1-Jan-1900, depending on whether you are talking Access or Excel). And by the same token, -1 is the value for TRUE in the byte integer used to represent a Boolean data type.

The OP's problem is simply that in the absence of formatting instructions, the VBA methods of examining that storage unit look at it as a BYTE rather than a Boolean. And my original discussion was to suggest using CBool to force the output routines to see it as a Boolean. Because in essence, using an unformatted output method to look at an unusually formatted variable results in a confusing answer.