Boolean Nulls

[Stuart McCall]

(Another pedeant rides in....) I don't think that is right, the untouched

value of a Boolean field is zero. I made a new table including a Text
Field
[Txt] and boolean field [Bool1]. Bool1.DefaultValue is Null. I added 5
records with "a" to "e" in [Txt] and leaving [Bool1] untouched. Querying
the
table shows the values for [Bool1] are all zero. Next I added a second
boolean field [Bool2] and queried again. The values for [Bool2] are all
zero.

Tim

It seems that in Access 'untouched' means strictly that, ie the boolean is
contained in a 'new record'. Open a form based on your table and navigate to
the new record. Switch to the VBE and type this in the immediate window:

?Forms!MyForm!MyField

making sure that MyField.Name is different to MyControl.Name, so you know
you're looking at the recordset value, not the control's value. I get Null
as the result. What do you get?

 

[Tim D]

(Another pedeant rides in....) I don't think that is right, the untouched
value of a Boolean field is zero. I made a new table including a Text
Field
[Txt] and boolean field [Bool1]. Bool1.DefaultValue is Null. I added 5
records with "a" to "e" in [Txt] and leaving [Bool1] untouched. Querying
the
table shows the values for [Bool1] are all zero. Next I added a second
boolean field [Bool2] and queried again. The values for [Bool2] are all
zero.

Tim

It seems that in Access 'untouched' means strictly that, ie the boolean is
contained in a 'new record'. Open a form based on your table and navigate to
the new record. Switch to the VBE and type this in the immediate window:

?Forms!MyForm!MyField

making sure that MyField.Name is different to MyControl.Name, so you know
you're looking at the recordset value, not the control's value. I get Null
as the result. What do you get?

So that's what 'untouched' means. I get the same as you. In fact the
control's value is also null, whether TripleState = True or False. I suppose
this is logical. I now remember finding this out x years ago when code
examined the value of a checkbox in OnCurrent. What is odd, is that on my
little test form, the checkbox never 'greys out' even when the value is null.
Hmmm.... never had that in reality so I'll just ignore it for now!

Tim

 

[Marshall Barton]

(Another pedeant rides in....) I don't think that is right, the untouched
value of a Boolean field is zero. I made a new table including a Text
Field
[Txt] and boolean field [Bool1]. Bool1.DefaultValue is Null. I added 5
records with "a" to "e" in [Txt] and leaving [Bool1] untouched. Querying
the
table shows the values for [Bool1] are all zero. Next I added a second
boolean field [Bool2] and queried again. The values for [Bool2] are all
zero.

Tim

It seems that in Access 'untouched' means strictly that, ie the boolean is
contained in a 'new record'. Open a form based on your table and navigate to
the new record. Switch to the VBE and type this in the immediate window:

?Forms!MyForm!MyField

making sure that MyField.Name is different to MyControl.Name, so you know
you're looking at the recordset value, not the control's value. I get Null
as the result. What do you get?

Try that experiment again, but after typing a single
character into the text field. I'm pretty sure that
everything in a new record is Null until the BeforeInsert
event fires.

 

[Tim D]

(Another pedeant rides in....) I don't think that is right, the untouched
value of a Boolean field is zero. I made a new table including a Text
Field
[Txt] and boolean field [Bool1]. Bool1.DefaultValue is Null. I added 5
records with "a" to "e" in [Txt] and leaving [Bool1] untouched. Querying
the
table shows the values for [Bool1] are all zero. Next I added a second
boolean field [Bool2] and queried again. The values for [Bool2] are all
zero.

Tim

It seems that in Access 'untouched' means strictly that, ie the boolean is
contained in a 'new record'. Open a form based on your table and navigate to
the new record. Switch to the VBE and type this in the immediate window:

?Forms!MyForm!MyField

making sure that MyField.Name is different to MyControl.Name, so you know
you're looking at the recordset value, not the control's value. I get Null
as the result. What do you get?

Try that experiment again, but after typing a single
character into the text field. I'm pretty sure that
everything in a new record is Null until the BeforeInsert
event fires.

Yes, that's what happens in my test form.
Tim D

 

[Stuart McCall]

Try that experiment again, but after typing a single
character into the text field. I'm pretty sure that
everything in a new record is Null until the BeforeInsert
event fires.

Yes you're right, that's the behaviour I get too. That's why I pointed out
that 'untouched' meant exactly that. However this appears to be the only
time a boolean can 'contain' Null, so John V is right, it's not a valid
value (that's how I now see it anyhow). This has been a learning experience.
I was initially misled by ambiguities in help (so what's new?

 

[Tim D]

Yes you're right, that's the behaviour I get too. That's why I pointed out
that 'untouched' meant exactly that. However this appears to be the only
time a boolean can 'contain' Null, so John V is right, it's not a valid
value (that's how I now see it anyhow). This has been a learning experience.
I was initially misled by ambiguities in help (so what's new?

Presumably a new 'record' is not really a record at all until it is
'touched' by entering something in a 'field' causing BeforeInsert to fire.
This would be consistent with the name 'BeforeInsert'. Learning experiences -
I have had at least one day since Access 1

 

[Linq Adams via AccessMonster.com]

Ambiguities in Micro$oft help! Yikes! Who'd a thunk it?

 

[Allen Browne]

Stuart, this has been an interesting thread.

To widen the discussion a little, Microsoft designed JET so that a yes/no
field is 2-state only, i.e. cannot store Null. But this doesn't work out in
practice: all you need is a yes/no field from the *outer* side of a join in
a query, and you do have Null in a yes/no field in the query. At this point,
if you try to operate on this field (group, sort, apply criteria, ...), JET
fails.

If you're lucky, you get a nonsense error message. If you're unlucky, Access
crashes. More info in:
Why I stopped using Yes/No fields
at:
http://allenbrowne.com/NoYesNo.html

 

[Stuart McCall]

Stuart, this has been an interesting thread.

To widen the discussion a little, Microsoft designed JET so that a yes/no
field is 2-state only, i.e. cannot store Null. But this doesn't work out
in practice: all you need is a yes/no field from the *outer* side of a
join in a query, and you do have Null in a yes/no field in the query. At
this point, if you try to operate on this field (group, sort, apply
criteria, ...), JET fails.

If you're lucky, you get a nonsense error message. If you're unlucky,
Access crashes. More info in:
Why I stopped using Yes/No fields
at:
http://allenbrowne.com/NoYesNo.html

Well I wasn't aware of that, and I don't think I have ever been bitten by
it. Something more I've learned. Thanks.

From your article:

"In the lower pane of table design, the field has a property named Display
Control on the Lookup tab. The interface does not offer Check Box as an
option"

I'm using A2003 on XP SP2 and I'm offered check box, combo box and text box
as lookup controls.

Having said that, I'm not keen on using lookup controls. I prefer to see
data in tables 'in the raw'. I'd much rather be presented with 0 and -1 than
a checkbox.

The article is certainly food for thought. I'll go through it again tomorrow
when I'm more wide awake (it's past my bedtime right now).

Thanks again for posting that.

 

[Tim D]

At least in SP3 and with simple Outer Join queries, I have found no problem
in applying criteria (0, -1, Is Null) to the outer Yes/No field. But, as you
say, Grouping fails. I have only used this myself during while importing/
massaging data and wouldn't trust it in an app.