Problem with report checkboxes and null values

Question

I'm having a problem with Access 2010 reporting.  I have a report that has checkboxes defined as tri-state.  The query contains an outer join, and the query is returning a yes/no field with three records with three values: yes, null, and no.  The report checkboxes are incorrectly reporting the results as yes, no, and no.  In other words, the null value does not get shown correctly in the tri-state checkboxes.  What obvious thing am I missing?

Answer 1

A checkbox will not store a Null value, therefore use a Textbox instead, see this article for reference:

http://allenbrowne.com/NoYesNo.html

 

Hope this helps,

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Answer 2

I believe that things have changed since 2007 -- tri-state checkboxes are supposed to be able to handle Yes/No/Null.

Answer 3

> I believe that things have changed since 2007 -- tri-state checkboxes are supposed to be able to handle Yes/No/Null. <

 

I think you missunderstood my answer.

Yes, a Tri-state checkbox will handle Yes/No/Null only when its bound to a Number ot TextField in a Table.

 

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Answer 4

Thanks!  After re-reading the info from the link, I believe I understand what's going on now.  The tri-state check boxes do seem to work correctly with number fields.  Too bad Microsoft won't take the time to get the boolean fields to accept null also.

Answer 5

Hi Wcalcote,

>>Too bad Microsoft won't take the time to get the boolean fields to accept null also.

Thank you for your valuable feedback. I will help you to submit it to the product team. Thank you for your understanding and wish you a nice day.

Best Regards,

---

Bruce Song [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 6

I will help you to submit it to the product team.

Not sure it makes sense. Boolean data type can only have 2 values by its definition. Even we look at the first coming in mind source (http://en.wikipedia.org/wiki/Boolean_data_type). And it's really unnecessary and even incorrect to make Boolean fields accept Null values. The answers like "yes", "no", "I don't know" are not already Boolean. Access is not Excel where you can select "Blank" and where it does not actually matter what is situated within the cells of one column. It is a strongly typed environment and I think it must remain this one.

---

Andrey V Artemyev | Saint-Petersburg, Russia The new recognition system is coming, check it right now http://blogs.msdn.com/b/addeditdelete/archive/2011/06/30/forums-recognition-update-coming-july-14th-2011.aspx

Answer 7

There are systems of logic in which the "I don't know" feature is useful. I don't think Wcalcote cares whether such systems are strictly "Boolean" in the traditional sense, what we want is for our programs to work—and there are applications for this sort of thing. I suspect you would share my antipathy toward the user-interface ergonomics of a nullable "check-box", but that would be the developer's problem, wouldn't it? In practice, the fact that this possibility is supported by SQL Server and other RDBMS systems means it should also be supported by the MS Access front-end.

---

Matthew Slyman M.A. (Camb.)

Answer 8

Matthew *triple-checking of correct name spelling*,

so, if SQL Server supports, then ok, let it be with Access too. It just my opinion because it breaks my inner logic. :)  

I noticed an interesting thing today working with WSS 3.0 (SharePoint 2007). I added a bunch of records (~2k) via Access which were originally stored in Excel. There was a boolean field, but my 'favourite' users left it blank everywhere. After transferring to SP where this field is Yes/No field, this field remains blank (null). But if we click "edit item" then "save" without any changes, this field becomes No. So, tri-state support is on the server side, but not on the client side. Just don't want to see the similar unexpexted behaviour in, let's say, Access 2012. 

---

Andrey V Artemyev | Saint-Petersburg, Russia The new recognition system is coming, check it right now http://blogs.msdn.com/b/addeditdelete/archive/2011/06/30/forums-recognition-update-coming-july-14th-2011.aspx

Answer 9

Andrey,

Don't worry about my name—that was originally just an arcane joke between computer scientists, about implicit type conversions (the joke was related to Latin/Cyrillic transliteration).

Yes, I would agree with you that if there are strange behaviours like this, I would not like to see this "functionality". Only once such problems have been fully resolved, please!

M.

---

Matthew Slyman M.A. (Camb.)

Answer 10

There are religious wars in the db world about whether Boolean fields should allow Nulls or not. There are holy wars about whether or not a well-designed db should even allow Nulls at all!

If you need to store three values, you can't use Boolean data type. It's really that simple.

It does change the logic of working with the values if you think of True as Not False (as you probably should, since that makes the value of True, 1/-1, irrelevant). However, just by allowing Null, you've lost the ability to use a single test for a Boolean value, so this may not be such a big deal.

---

David W. Fenton
David Fenton Associates