IIF returns 0 instead of boolean (1 Viewer)

rnickel

New member
Local time
Today, 04:01
Joined
Nov 20, 2019
Messages
2
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

MVP / VIP
Local time
Today, 11:01
Joined
Jan 14, 2017
Messages
18,186
@rnickel
Welcome to AWF.
Did you realise this thread was 11 years old?
 

rnickel

New member
Local time
Today, 04:01
Joined
Nov 20, 2019
Messages
2
@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.
 

fertooos

New member
Local time
Today, 04:01
Joined
Dec 8, 2019
Messages
8
Boolean data is stored as 0 or 1.
1 = Yes, True, or On.
0 = No, False, or Off.
 

isladogs

MVP / VIP
Local time
Today, 11:01
Joined
Jan 14, 2017
Messages
18,186
Actually Boolean values in Access are 0 and -1.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:01
Joined
Jan 20, 2009
Messages
12,849
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

MVP / VIP
Local time
Today, 11:01
Joined
Jan 14, 2017
Messages
18,186
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

Nifty Access Guy
Staff member
Local time
Today, 11:01
Joined
Jul 9, 2003
Messages
16,245
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

MVP / VIP
Local time
Today, 11:01
Joined
Jan 14, 2017
Messages
18,186
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
 

Users who are viewing this thread

Top Bottom