IIF returns 0 instead of boolean (1 Viewer)

[rnickel]

Here is the actual answer to what the OP wanted to do, which is the same as what I was trying to do: namely, display Boolean values in a calculated column as checkboxes instead of numeric values. (I was using IsNull rather than IIf, but had the same issue.)

1) In table design view, select the calculated column in question.
2) In the "General" tab, set "Result Type" to "Yes/No"
3) In the "Lookup" tab, set "Display Control" to "Checkbox"

 

[isladogs]

@rnickel
Welcome to AWF.
Did you realise this thread was 11 years old?

 

[rnickel]

@rnickel
Welcome to AWF.
Did you realise this thread was 11 years old?

I did, yes. But it's the top result that comes up in Google on the question it raised, and it still didn't have a single response that actually just answered the question; only three pages of the usual "why do you want to do it *that* way?" So, I figured: high time.

 

[isladogs]

OK thanks for helping out.

 

[fertooos]

Boolean data is stored as 0 or 1.
1 = Yes, True, or On.
0 = No, False, or Off.

 

[isladogs]

Actually Boolean values in Access are 0 and -1.

 

[Galaxiom]

Actually Boolean values in Access are 0 and -1.

BTW In case anyone is wondering why -1 in particular would be chosen in Access as True, rather than the probably more expected value of 1.

Minus 1 makes sense because it is stored with all the bits in the binary number on, while they are all off for zero (False). You could test any bit in a Boolean and get a consistent answer.

If curious, check out Two's Complement method of storing signed numbers in computers. It simplifies the arithmetic operations.

 

[isladogs]

One more thing related to the above.
If you enter any integer other than 0 into a Boolean field, it is treated as meaning true and saved as -1.
So for that reason, it is sometimes stated that 0 is false and anything else is true.

 

[Uncle Gizmo]

Boolean data is stored as 0 or 1.
1 = Yes, True, or On.
0 = No, False, or Off.

Well in some languages it could well be 1 and 0, but in VBA it's 0 and -1 ... But that's not exactly true either, if you read this Microsoft help file "Boolean" is itself "Boolean" - not a number. It's the conversion of the Boolean to numbers which becomes:-

False = 0
True = -1

Extract:-
When Visual Basic converts numeric data type values to Boolean, 0 becomes False and all other values become True. When Visual Basic converts Boolean values to numeric types, False becomes 0 and True becomes -1.

And if you're using check-boxes, and I think this only applies to Microsoft Access check-boxes, (but I'd need to look it up to check) check boxes can have three values, true, false and something else. I'm not sure if that in between state is stored? But I'm not going there today! something for you to research, if you're interested in it...

 

[isladogs]

Unbound check boxes in Access can have a triple state where the third state is null. It appears as a black filled box.
However check boxes bound to Boolean fields can only be true or false.

In SQL Server boolean fields can also be null. This can cause write conflict errors when used as a BE in Access unless a default value is set in SS