Boolean as Null (1 Viewer)

jaryszek

Registered User.
Local time
Today, 15:14
Joined
Aug 25, 2016
Messages
756
Hi,

is is possible to set defaults for "Yes/No" boolean?

I would like to have Null for No.

And if it is possible "ENABLE" for Yes.

Best,
Jacek
 

isladogs

MVP / VIP
Local time
Today, 22:14
Joined
Jan 14, 2017
Messages
18,186
You can set the default value for access boolean fields to either true or false.
Boolean fields in SQL Server can also be null
Null is NOT allowed in Access

NOTE:
1. However unbound checkbox controls can also be null

2. You will get write conflict errors in Access action queries when using SQL booleans with null values
 

jaryszek

Registered User.
Local time
Today, 15:14
Joined
Aug 25, 2016
Messages
756
hi Colin,

thanks.
Hmm so the best option is to use query with if statement instead of creating strange values as default for boolean?

Jacek
 

Minty

AWF VIP
Local time
Today, 22:14
Joined
Jul 26, 2013
Messages
10,355
The other alternative is to simply use an integer value for the field, which can be null in access. You can still apply Boolean logic to it. 0 is false, anything else (other than null) is true. Null will return Null .
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:14
Joined
Feb 28, 2001
Messages
27,001
Using Null in ANY comparison is usually asking for trouble because the following expression is TRUE in Access: Null <> Null

Null cannot be compared to anything including another null because WHATEVER the math, they are not equal to each other. Null trips up math expressions so fast it will make your head spin. You can TEST for Null with IsNull(X) which is TRUE or FALSE, or you can do an on-the-fly substitution using the NZ(X,default) function. Don't use Null as a meaningful value if the meaning is ANYTHING other than "no value is available." Assigning an interpretation to null other than that invites trouble in the form of sloppy programming.

This thread discusses use of nulls at great (and eventually boring) length:

https://access-programmers.co.uk/forums/showthread.php?t=160065
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:14
Joined
Oct 29, 2018
Messages
21,358
Using Null in ANY comparison is usually asking for trouble because the following expression is TRUE in Access: Null <> Null

Null cannot be compared to anything including another null because WHATEVER the math, they are not equal to each other. Null trips up math expressions so fast it will make your head spin. You can TEST for Null with IsNull(X) which is TRUE or FALSE, or you can do an on-the-fly substitution using the NZ(X,default) function. Don't use Null as a meaningful value if the meaning is ANYTHING other than "no value is available." Assigning an interpretation to null other than that invites trouble in the form of sloppy programming.

This thread discusses use of nulls at great (and eventually boring) length:

https://access-programmers.co.uk/forums/showthread.php?t=160065
Just making sure we revisit the above statement in red. The result of comparing Null to anything should also be Null.
 
Last edited:

plog

Banishment Pending
Local time
Today, 17:14
Joined
May 11, 2011
Messages
11,612
the following expression is TRUE in Access: Null <> Null

I think Doc just mistyped that. His overall message is "don't trust Null", which is correct.

Null <> Null resolves to False in Access:

TestTable:
id, a, b
1, 1, 1
2, 1, Null
3, Null, Null

Code:
SELECT a, b, IsNull([a]) AS a_Is_Null, IsNull([b]) AS b_Is_Null, IIf([a]=[b],True,False) AS [a=b], IIf([a]<>[b],True,False) AS [a<>b]
FROM TestTable;

Results:

id, a, b, a_Is_Null, b_Is_Null, a=b, a<>b
1, 1, 1, 0, 0, -1, 0
2, 1, Null, 0, -1, 0, 0
3, Null, Null, -1, -1, 0, 0
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:14
Joined
Oct 29, 2018
Messages
21,358
I think Doc just mistyped that. His overall message is "don't trust Null", which is correct.

Null <> Null resolves to False in Access:

TestTable:
id, a, b
1, 1, 1
2, 1, Null
3, Null, Null

Code:
SELECT a, b, IsNull([a]) AS a_Is_Null, IsNull([b]) AS b_Is_Null, IIf([a]=[b],True,False) AS [a=b], IIf([a]<>[b],True,False) AS [a<>b]
FROM TestTable;
Results:

id, a, b, a_Is_Null, b_Is_Null, a=b, a<>b
1, 1, 1, 0, 0, -1, 0
2, 1, Null, 0, -1, 0, 0
3, Null, Null, -1, -1, 0, 0
Thanks for trying to clarify, but I will say it again. Comparing anything to Null should result in Null - not True and not False either, just Null. Please try the following query and let us know the result:
Code:
SELECT a, b, a=null, b=null, a=b, a<>null, b<>null, a<>b FROM TestTable
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 22:14
Joined
Jan 14, 2017
Messages
18,186
Some examples from the Immediate window

 

Attachments

  • Capture.PNG
    Capture.PNG
    2.8 KB · Views: 749

plog

Banishment Pending
Local time
Today, 17:14
Joined
May 11, 2011
Messages
11,612
Excellent code, I got the same results as isla and now see your point.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:14
Joined
Feb 28, 2001
Messages
27,001
So actually, I slightly misspoke. The expression Null <> Null resolves to Null, not TRUE. But nonetheless, even Null = Null is not TRUE. You just cannot compare Null to anything and expect anything back but Null.

Therefore, using Null as a "value" is adding extra work for the required IsNull case. If you REALLY need TRUE, FALSE, and something else, make it an encoded BYTE integer with translations for TRUE, FALSE, and SCREWED THE POOCH (or whatever you need it to mean.)

Doing it as an encoded byte, perhaps using an ENUM list, gives you up to 256 possible values that don't involve null. In my biggest database, that strategy was valid for my worst possible status holder that had 22 possible statuses that needed to be crammed into the smallest possible record. And even that SHOULD have been done differently, but I was scrimping on space because of long-term record-keeping requirements mandated by our security wonks.
 

isladogs

MVP / VIP
Local time
Today, 22:14
Joined
Jan 14, 2017
Messages
18,186
I am not sure what it does, but one of the options with a yes/no field is "triple-state".

I think the triple-state includes a null

eg
https://www.experts-exchange.com/articles/10740/Access-Forms-Triple-State-Checkboxes.html

No.
As already stated in post #2, Yes/No fields can only be true or false in Access.
Similarly for BOUND checkboxes.
However UNBOUND checkboxes can use the triple state to indicate null.
In that case, the checkbox appears 'filled in'

In SQL Server, null is also possible for boolean fields

EDIT: just looked at the link which says the same thing as I have
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 22:14
Joined
Sep 12, 2006
Messages
15,614
@colin

I should have read down to the bottom of the link
I just assumed there was a way!
 

Users who are viewing this thread

Top Bottom