DataBase - Yes/No Data Types

Asked By Peter Marshall

17-Apr-07 08:28 AM

I have defined a Yes/No data type field with a Default Value of nothing
(neither Yes nor No).  However, when records are created without indicating
Yes or No for that field and I query the Yes/No field, they act as if they
are a No, when in fact I thought they would be a null.  What's the trick
with Yes/No fields?

Peter Marshall
Manager Information Services
Ohio Coatings Company
(740) 859-5560 office
(304) 281-7404 cell

17-Apr-07 09:54 AM

This might be a Mission Impossible. A Yes/No field can contain only two
values: Yes or No. If you don't put anything in them, they default to No
(which is actually stored as 0).

Access does support triple state checkboxes but not at the table default
level. You can read more about it here:

http://www.databasedev.co.uk/triplestate_checkbox.html

Personally I think that this is a misuse of Yes/No fields as boolean logic
says that it's one or the other. You might want to consider an Option Group
instead if there can be three values.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

17-Apr-07 10:16 AM

As Jerry explained, the Yes/No data type in Access is capable of handling 2
states only. Null is not supported.

To get a 3-state field, use a field of type Number, size Integer, with the
Display control set to Check Box, and the check box's TripleState property
set to Yes. In Access 2003 or 2007 on Windows XP or Vista, you may want to
turn of Windows Themed controls: otherwise you cannot see an visible
difference between False and Null.

JET's inability to handle nulls is a major problem in Access. Of course, you
still get Null in Yes/No fields (e.g. in a query with an outer join.) If you
then try to operate on the yes/no field, Access will crash. Or if you are
lucky, it just gives senseless errors such as this example:
http://allenbrowne.com/bug-14.html

It may be wise to avoid yes/no fields all together, and use Integer fields
instead, so as to avoid these bugs and crashes.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.