Can a boolean ever be set to null?

[JimBurke via AccessMonster.com]

I have a field in a table that I would like to be Boolean, but I need to be
able to have possible values of True, False and Null. Is this possible, or do
I have to use a numeric field with, say, 0,1,2 or something like that? I need
to know if the value has never been set by the end user - there is a form
where they will select True or False, and they should only be forced to enter
it once.

 

[JimBurke via AccessMonster.com]

The reason I'm asking is that I created a new boolean field in a table and
all the values are set to 0. So I ran a query to set them to Null and they're
still 0. So it seems that it won't set the value to Null regardless, but I
thought maybe there was SOME way to do it.

 

[Allen Browne]

In your table, use a Number field instead of a yes/no field.

Numbers can be null, where yes/no fields in JET tables cannot. If you use -1
for Yes and 0 for No, your number field will contain the same values as JET
uses for yes and no.

I *always* use Number fields instead of yes/no fields, so here's more info
about how to use number fields instead:
Why I stopped using Yes/No fields
at:
http://allenbrowne.com/NoYesNo.html

 

[Douglas J. Steele]

Boolean fields in tables cannot be Null.

However, since you should always be using forms to interact with the table,
you should be checking in the form's BeforeUpdate event to see whether the
check box has been entered, rather than worrying about the value in the
table.

 

[Jack Leach]

The Boolean datatype will always default to False, as will a Yes/No field in
a table. Both the Boolean datatype and a Yes/No field convert to an integer
on a system level (0 is false (default) and -1 is true (I think maybe even
anything except 0 equates to true)).

Allen has some insight to this:
http://allenbrowne.com/NoYesNo.html

Personally I use a byte field with no default value (null's for all new
records) for situations such as these.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Klatuu]

If there is a business requirement to have a boolean table field contain 3
vaules, then Doug Steele's suggestion will work for you; however, when you go
beyond 2 states (True/False), it is logically no longer a Boolean field.

Note, however, if the object is to ensure a user makes a selection on the
form, then you can set the check box's Triple State proerty to yes. It will
default to a Null value and only contain a valid value when the user makes a
selection (or you do it in code). If the control is bound to a field, it
will throw an error if you try to update the record, so you would need to use
the form's before update event to ensure the check box is not Null.

 

[JimBurke via AccessMonster.com]

Thanks for the replies. My first thought was to use a byte field instead, so
that's what I'll do. I have reports that are printed each day for the doctor
for the patients he/she will see that day. If the patient has never been
asked if they smoke then the report has to have the question printed on it
'Does patient smoke?'. Once the question is answered for a patient, it gets
entered into the patient information and the question should not show up on
that report again for that patient. I'll need to make it a radio button on
the patient information screen, so that there are selections for Don't Know
(default), Yes and No, and make sure they know to enter the value into the
system from the doctor's encounter form (what that report is called).

 

[Klatuu]

Good plan. You will be using an Option Group, then.

 

[JimBurke via AccessMonster.com]

Right - that's what I meant when I said radio button.

Good plan. You will be using an Option Group, then.

Thanks for the replies. My first thought was to use a byte field instead, so
that's what I'll do. I have reports that are printed each day for the doctor

[quoted text clipped - 13 lines]

 

[JimBurke via AccessMonster.com]

Right - that's what I meant when I said radio button. Does Access not allow
Null for a boolean because it only examines one bit when it's dealing with
boolean fields? Just curious as to why they can't be Null. That's the only
reason I can think of.

Good plan. You will be using an Option Group, then.

Thanks for the replies. My first thought was to use a byte field instead, so
that's what I'll do. I have reports that are printed each day for the doctor

[quoted text clipped - 13 lines]

 

[Dirk Goldgar]

Does Access not allow
Null for a boolean because it only examines one bit when it's dealing with
boolean fields? Just curious as to why they can't be Null. That's the only
reason I can think of.

I'm not sure, but I suspect that all the boolean fields in a table may
actually be store in a block of bits, with one bit per field. This is
purely conjecture, but it would be a space-saving measure. In such a case,
there would be no way to indicate that the field is Null.

 

[Jack Leach]

If I remember correctly, the Byte is the datatype that takes the least amount
of bits to store, which struck me as odd... you'd think a boolean would be a
single bit, either 0 or 1, right? apparently that's not the case though. A
boolean actually takes more bits to store than a Byte, which can be one of
255 numbers.

So I would think, based on that, that there would be plenty of room in
memory to store whether a boolean would be true, false, or null.

The workings of bits is way beyond me, but a boolean takes more memory to
store than 0-255, for whatever reason.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Dirk Goldgar]

If I remember correctly, the Byte is the datatype that takes the least
amount
of bits to store, which struck me as odd... you'd think a boolean would be
a
single bit, either 0 or 1, right? apparently that's not the case though.
A
boolean actually takes more bits to store than a Byte, which can be one of
255 numbers.

So I would think, based on that, that there would be plenty of room in
memory to store whether a boolean would be true, false, or null.

The workings of bits is way beyond me, but a boolean takes more memory to
store than 0-255, for whatever reason.

I believe the VB Boolean data type is stored in memory as an Integer. But I
don't think it is stored that way in the table.

 

[Allen Browne]

Disadvantages of using a byte:
- unsigned, so can't store the value JET normally uses for True
- slower execution than Integer or Long.

 

[Jack Leach]

Disadvantages of using a byte:
- unsigned, so can't store the value JET normally uses for True
- slower execution than Integer or Long.

This is interesting news. I always thought the byte would be faster due to
the fact that it's a fraction of the size. I never knew the difference
between signed and unsigned either (apparently signed equates to the ability
to go positive/negative, in laymans terms?).

Apparently Jet must have some serious integration for Integers and Longs,
but not for bytes? Interesting that a Long Integer, with thousands more
available combiations of numbers, would process faster than reading a byte
with 256 available numbers.

In the meantime, between trying to find out how a number thousands of times
larger equates faster, I should probably see about going back and changing
various status fields to an integer rather than a byte...

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Allen Browne]

Right: unsigned = positive only.
In JET (and VBA), bytes are always unsigned (0 ~ 255.)
Integers and Longs are always signed.

The performance difference is due to the fact that our processors,
compilers, and software are optimized for 32-bit operations. 16-bit integers
will be around the same speed, but they are not optimized for byte
operations. I understand this may seem counter-intuitive.

(Actually, it's been several years since I actually tested this. At the
speed of current processors, the speed difference might be academic unless
you are doing a pretty intensive loop.)

 

[Marshall Barton]

This is interesting news. I always thought the byte would be faster due to
the fact that it's a fraction of the size. I never knew the difference
between signed and unsigned either (apparently signed equates to the ability
to go positive/negative, in laymans terms?).

Apparently Jet must have some serious integration for Integers and Longs,
but not for bytes? Interesting that a Long Integer, with thousands more
available combiations of numbers, would process faster than reading a byte
with 256 available numbers.

In the meantime, between trying to find out how a number thousands of times
larger equates faster, I should probably see about going back and changing
various status fields to an integer rather than a byte...

The time is related to the processor's data width. With an
8 bit processor (not used for desktop machines since the
early '80s) a byte was most efficient, but today's 32 and 64
bit processors make a word or double word the most
efficient. A 16 bit integer is very slightly less efficient
to use, but don't forget that with gigahertz machines, we're
talking about fractions of a nanosecond.

OTOH, the time it takes to retrieve the greater number of
bytes from the hard disk is probably a lot greater than any
calculation time saved.

Bottom line, the unsigned aspect is more important. Use an
Integer to get a signed value and don't waste a brain cell
worrying about the time to deal with one byte vs two bytes.

 

[Steve Sanford]

That is what I like about this NG..... I learn things that I no longer have
to worry my brain cells about.

Soooo, time to kill a few spare cells!! Where's my beer?????

Have a great weekend!!

 

[Jack Leach]

That is what I like about this NG..... I learn things that I no longer have
to worry my brain cells about.

Soooo, time to kill a few spare cells!! Where's my beer?????

Great isn't it?

Thanks guys for stifling my curiousity it areas that I probably wouldn't
even use for a game of jeopardy. I still can't seem to be satisified not
knowing, even when knowing probably doesn't make any difference at all.

Have a great weekend!!

Happy IDay to those effected!

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)