Yes/No ....... NO!!!!

[Deleted]

An interesting article by Allen Brown highlights why you should avoid using Yes/No data fields in MS Access databases. His advice makes a lot of sense, and I’ll be adjusting my practices accordingly going forward.

Microsoft Access tips: Code accompanying article - Has the record been printed?

A workaround for a bug where Microsoft Access crashes or fails to execute queries with Yes/No fields.

allenbrowne.com

 

[CraigDolphin]

Interesting article...thanks for the link!

 

[The_Doc_Man]

Having read the article, I don't think I completely agree with the premise and therefore have problems with the conclusions.

First, for a non OUTER JOIN case, assert a default value of FALSE for all Yes/No fields and you are good. That way, you have no nulls anywhere for any single-table record with a Yes/No field or any INNER JOIN with an incompletely defined record. See further discussion below about incomplete records.

Second, if you are going to assert an OUTER JOIN that you already knew included a yes/no field, you should include appropriate safeguards in the query. If worst comes to worst, I think an IIF IsNull( [yesno field] ) still works and allows you to assert the value you wanted in that case. Or you can do a UNION query of the records that JOIN correctly plus a second select that supplies appropriate constants for those records having no matching records.

SELECT [A],, etc. etc. FROM [T1], [T2] ... WHERE .... AND DCount of {matching records from T2} > 1 UNION
SELECT [A], constant for missing , etc. etc. FROM [T1] WHERE .... AND DCount of {matching records from T2} = 0 ;

OUTER JOIN? Who need's 'em?

Third, whether right, wrong, or indifferent, many native Access structures still depend on the use of Yes/No fields for which making it something else (like using a transliterated or typecast BYTE field) adds extra work. The only question is "will you pay now or pay later?"

OK, not to denigrate Allen - but the problem he describes should never have occurred because it basically implies that NULL fields have meaningful values in other cases. To me, this is a design philosophy issue that violates the complete statement of Murphy's First Law.

Please note that I am of the school that says "When NULL is a meaningful value, you have been lazy with your design." This is a major part of the basis for my disagreement with the article. It implies that there are uses for NULL but then complains that YES/NO fields don't play by that rule. Yes, an outer join is legal in SQL and having a null is a valid indicator that there was nothing for the join to catch from the dependent side of the JOIN. But that should be all it says, and in that case, NONE of the other fields should count for anything. This is just inviting a Murphy's First Law failure. Sometimes I can be harsh in my viewpoint and I really don't apologize for it when the issue is basic mental indolence.

What? You don't know the COMPLETE statement of Murphy's First Law?

"If something can go wrong, it WILL go wrong." Yeah, you knew that part. But here is the rest of it... "Therefore, design it so that it cannot go wrong." Interested parties could look up Ed Murphy in conjunction with the White Sands Rocket Sled experiments of the 1950's to learn more about his laws.

My own favorite is the chemist's variant:

"Heavy objects will violate the law of gravity and not fall straight down if in falling sideways they can strike a fragile, expensive, and difficult-to-replace bit of glassware somewhere in the same space-time continuum as the falling object. Further, the object will only strike the glassware when it encloses superheated, explosive, corrosive, poisonous, radioactive, and/or expensive chemicals such that the cost of chemicals, glassware, and cleanup will come out of the technician's pocket."

 

[CraigDolphin]

Please note that I am of the school that says "When NULL is a meaningful value, you have been lazy with your design."

I have always thought null meant unknown, or not known. For example, for salmon spawners surveys we use a check box to indicate whether a carcass is adipose fin-clipped or not. But sometimes the carcass has rotted, or been partially eaten, and the rear-portion of the carcass is missing. In this case, null is meaningful in that we don't know if the salmon was adipose fin-clipped or not.

So I use a number field with a triple state checkbox on the form to enter this information because yes/no doesn't allow nulls (as I learned from earlier failed attempts to use yes/no fields). When summing our results, I want to enumerate carcasses that were adclipped, non adclipped, and also those we could not determine ad-clip status.

Are you saying my approach here is incorrect?

 

[gemma-the-husky]

yes/no and nulls in general

i use booleans a lot (perhaps learning to programme with Pascal caused this), and will continue to do so - i do try to ensure that the yes/true setting is the thing i want to test for so i dont get caught by false positives - ie a false becuase its false or false because its just not set as true.

i actually think its beneficial not to have nulls - i dont like them and in many ways would rather be forced to cast a value, although i agree its possibly awkward when a zero cant then be distinguished from a null-zero

not quite the same thing, but the only time i really got caught with access was when i was trying to use a date field to verifiy another date entry - the idea being that if the date was null, then any date was valid, but if not null, then only the same date was valid

so heres the catch - the null date was part of a composite key which was set to no duplicates - but where the date part of the key was null, then duplicate keys were written to the file!

instead i have had to store a checkdate of zero, and explicitly test for zero rather than null in the date check

i think ive read here (or maybe elsewhere) that the cardinality of indexes can affect performance adversely, so indexing a yes/no field (which i do) may not be the best thing to do.

 

[The_Doc_Man]

Craig Dolphin

Are you saying my approach here is incorrect?

Possibly, though as with ALL (and I do mean ALL) applications, if you take appropriate pains to safeguard yourself, it's all fair game. What works for you works for you and doesn't have to work for anyone else - including me. My somewhat harsh opinion and a couple of bucks still only gets you one small cup of gourmet coffee. So take it cum grano.

I simply dislike the philosophy that says "NULL" means something - AS A VALID MEANING FOR AN APPLICATION. If ANY part of a record is defined, it should ALL be defined. Only virgin records can be null. (Not to be confused with Virgin Records, which has a nice selection of CDs... but I digress.)

Anyway, if you visit the record, define everything. With the caveat that it is OK to revisit and update the parts of the record for which you earlier supplied non-committal values.

Take, for example, your adipose fin clipping... Use a radio button with values Clipped, Not Clipped, Damaged, and (default) Not Recorded. You can fit this many state values into a byte if you wish - same size as a Yes/No but with more versatility. The radio button works fine and makes your choices unequivocal. You can give this radio button group a default value that equates to "Not Recorded." No problems with that approach. And no NULLs.

To me, tri-state logic where NULL is one of the states is just a somewhat lazy shortcut. OK, it was your call to choose what you chose and only you know the pressures you were under to get it done as quickly as possible.

Being a pragmatist at heart, I rarely kvetch too loudly about someone else making a pragmatic decision. But when it comes to theory, I can still suggest that in general, use of NULL as an extra state code is a lazy and dangerous approach that will be inconsistently handled if you ever change your underlying database engine. I.e. someday you might change to FE/BE where the BE is NOT Access. At which time you would have to revisit EVERY FIELD where NULL was one of the possible cases. And hope you haven't forgotten all the cases. Pay me now, pay me later...

 

[CraigDolphin]

Doc_Man.

thanks for the reply and valuable food for thought. And incidentally, I don't consider your opinions harsh. You are always able to articulate the reasons for your opinions which is invaluable to folks like me who are new to this. To paraphrase: learn from you now, or learn for myself later...

I had thought about providing values for 'not recorded', 'damaged', etc, except then I figured I'd need to be able to forsee every possibility that might arise. I thought using a null was a way to avoid forcing a user to choose an option that might not fit the unforseen situation. In my view I consider bad/wrong information to be worse than no information. That said, I think the options you gave should cover things well enough. Maybe you're right...I was being lazy. In my defence, I'm a biologist not a db professional!

However, the point you made about switching to a non-access backend just scared the crud out of me. I have no plans to do such a transition but I do want it to be possible if necessary.

 

[Rich]

I simply dislike the philosophy that says "NULL" means something - AS A VALID MEANING FOR AN APPLICATION. If ANY part of a record is defined, it should ALL be defined. Only virgin records can be null. (Not to be confused with Virgin Records, which has a nice selection of CDs... but I digress.)

But isn't there a case say within a record that not every field has to have a value in, in which case there may be nulls in certain fields?

 

[Niniel]

But isn't there a case say within a record that not every field has to have a value in, in which case there may be nulls in certain fields?

Although this doesn't seem to have anything to do anymore with the original Yes/No field issue, I too am wondering now - if I have a field in my db that could routinely be left empty, ie contain null, then should I redesign the db to store the information that might go in that field in a separate table?

 

[liddlem]

Hi CraigDolphin
Just a different thought here.
I am currently working on an "Incident" DB where users need to indicate one or multiple causes and possible actions/solutions to an incident.

When looking at the physical (paper) form that a user fills out, these 2 areas (Causes - 28 possibilities AND Actions/Solution - 19 possibilities) are represented by a bunch of tick boxes that the user can select.

It is therefore possible that an incident occurs as a result of more than one cause (eg:"Not using correct lifting techniques" AND "Slippery Floor")
And also that more than 1 actions ("Retrain" the person to use proper techniques AND "Perform OH&S inspection" AND "Replace damaged items") should be taken to avoid a similar incident in future.

In order to make the capture process easy, my inital thinking was to create a seperate boolean field for each checkbox.

Big mistake ! - As i found out when I thought I was nearing the end of the project and putting the reports together.

For me - This raised a problem in that I need to generate stats reports (With Charts) that highlight common incidents. If you have ever worked with charts, you would know that a chart creates a crosstab query for your data. This in turn meant that I could not get the chart data to display as expected.

After a complete re-work, (My entire table structure and their relationships have changed) I have finally settled on the following solution.
I removed all the booleans from the TblIncidents.
Created a TblCauses with 2 fields (IncID, CauseDescr)
Created a TblActions with 2 fields (IncID, ActDescr)
Both linked back to the TblIncMaster.IncID field.

On my form, I have 28 and 19 button that are labelled as per each option.
When a button is pressed, the btn.caption is written to the respective table and font color changes to Red. (To indicate that it has been selected)
the reverse happens if a Red button is clicked. (Record deleted etc)

On the forms "On Current" event, I check if an "IncID/*Desc" exists for each of the buttons. If it does, change the font colour accordingly.

But the beauty about this new design is that I can now generate charts with almost any combination of Causes and Actions that I might want.

I realise that this is a far cry from the point that was being made in this post initially, but maybe this would serve as a good reference tool for those who might be tempted (like me) to supposedly - take the easy way out and use checkboxes.

 

[gemma-the-husky]

yep

it looks like the current arrangement is more properly normalised than the spreadsheet type layout you might have used.

the other drawback with the spreadsheet layout is that its a lot of work to add another test or check, and presumably its not so hard now.

 

[The_Doc_Man]

Rich, your question is quite valid. However, in my viewpoint, which is admittedly mine and not shared by all, this is an invitation to trip on something later. The thing that I always consider is that when you use NULL as the 257th value for a byte with 256 states, what do you do when state number 258 is discovered?

There is one and only one reason to have a null "somewhere" and that is because you have never, ever visited that "somewhere" before. I.e., you have never instantiated the thing it represents. Once it has been instantiated you need to consider more positive ways to say "nothing there". How do I tell the difference between "never been there" and "there was nothing to see when I got there" ??? This can be a BIG difference in the way you want to treat something.

I have tried to be consistent on this point with any discussions that impinge on the topic. I come originally from an Operations, Security, and Operating Systems background where having a record with a NULL in it gets you gigged hard. Try telling a security officer "Oh, it is null - we must not have checked this feature." Wanna see your system get isolated on the network until your reports are complete? 'tain't fun to scramble under that situation. Or your OpSys crashes because one of the links led to a null. Or your paycheck is short because you cannot document what you were doing - the timesheet was NULL in some places.

If you study computer language theory, you note that a major portion of some languages that allow complex data structures (and Access record structure certain meets the latter criteria) are the INSTANTIATORS and DESTRUCTORS. That is, what code underlies the object that is the moral equivalent of a Forms_Load or a Forms_Unload event? (But for a structure...)

Pascal, Ada, C and its variants, and several other modern languages - and many languages derived from the ones I just named - include facilities for object-structure creation and destruction. Access has a "default value" option for fields as well as referential integrity for child-record creation, so at least you can see to the instantiation. Referential Integrity with cascading delete comes close to the "destructor" part of the facility. Every language I know addresses the issue except for assembly language, BASIC (pre-VBA), and a couple of older oddballs.

I guess I'm rambling a little bit, but I spent 35+ years in this industry learning how to not make quite so many terrible mistakes. Forgetting to properly define data elements is usually a big mistake. I now have lots of experience. You do know the Ambrose Bierce definition of experience, don't you? "Experience is what allows you to recognize your mistakes when you make them again."

You've seen me discourse on the "Old Programmer's Rules." You get to be an "old programmer" by following the rules. You get to be a FORMER programmer by not following the rules. (You get to be a manager by not knowing there were rules or thinking that they didn't mean anything ... - there I go again, being unkind.)

In any case, tending to the creation and deletion of instances of your data objects is a non-trival operation that can be a great preventer of disasters. And the NULL element issue is a disaster waiting to happen.

In the final analysis, if you really wanted to use NULL for something, go right ahead. Just be initimately aware of the exorbitant price you pay in later code (or in later conversion to other platforms) if you were careless with NULLs.

 

[The_Doc_Man]

liddlem - after looking over your post, I think you've got the right idea.

Note the items checked. Ignore the ones not checked. Create entries in your child table to represent the check marks. JOIN to the checks. If you MUST know whether record A is associated with checkbox B, do a JOIN query on the two, then do a DCount of the attribute you want from the JOIN record of the attribute and the parent record. Doing it the way you are doing it, you can have any number of special check-box attributes.

I commend you for heading towards a more normalized database structure.

 

[The_Doc_Man]

In later reviewing of my post #12 in this thread, I see I ranted a little. I guess the issue is that I have a "hot" button relating to people not taking the time to correctly examine their designs. So often I see that in my government environment that I cringe whenever someone says "Launching a new project." If any of you thought I was specifically targeting you for some oversight in your designs, please don't take it that way. I forgive you for being human - if you will forgive me for the same little foible.

It is just that when someone pontificates about doing away with a particular variable type because it doesn't conform to his world view, the absurdity of the statement becomes more than just a little obvious. Maybe it is HIS view that is deficient, not the particular language or program. After all, Access is what it is. It isn't strawberries and cream. It is a fairly useful, sometimes very perverse product that happens to have a wide array of users and designers. But asking it to be fish when we know it is fowl... I have to remember to be civil.

Which doesn't mean I won't go off again when someone does something stupid enough to betray his/her ignorance yet again. But the only person who really should be upset is Allen, and then only if he happens to read this thread.

 

[Uncle Gizmo]

>>>stupid enough to betray his/her ignorance yet again<<<

I can't really see where you are coming from, I may be reading into your post things that arn't there.

However in my opinion the gentlemanly thing to do would be to e-mail Allen and discuss it with him. I'm sure that way you would both learn something, Allen would have the opportunity to educate you, and you would have the opportunity to educate him, both of you would gain something.

Running somebody down like this in a public forum does little for the way people perceive you.

 

[sphere_monk]

I may be missing something here, as I am self-taught on Access, but isn't the fact whether the Yes/No field is designed to hold a NULL value rather a moot point?

In Allen's example, because the query contains an Outer Join, the field would contain a NULL just from there being the absence of a record in the joined table. Taking the precautions of designing the table in the manner he suggests would avoid any errors that would arise in these situations.

I use many Outer Joins in the databases I have programmed. Is there a better way?

 

[gemma-the-husky]

i was looking at a thread about decompiling and i found my way to everythingaccess.com, which included references to a lot of Allen Browne's wisdom (i assume its a he, and not a company name). having read his views on nulls again (along with lots of other very useful thoughts), - he clearly regards nulls as most important

Nulls are not a problem invented by Microsoft Access. They are a very important part of relational database theory and practice, part of any reasonable database. Ultimately you will come to see the Null as your friend.

i don't think anyone would decry Allen's views. its just that it can be a pain sometimes having to bear in mind the need to test for null's as well as particular values.

 

[The_Doc_Man]

Like I have said before, I can be harsh sometimes. Life is too short for me to worry about political correctness. And those who know me well know that I can be rude, crude, tacky, and vulgar. At my age, I'm happy to be anything at all, I guess...

Uncle G, I have some limitations about e-mailing Allen given that right now I am connecting here from a military site because I'm having a p|ssing match with my service provider at home. I will consider offering him the benefits (???) of my opinion later, perhaps. I just cannot do so from this site because I'm not allowed to use military mail for non-military purposes. The forum is barely within the fringe of acceptability because we DO use Access on site and I am one of the maintainers for such things. But as a matter of ethics, I cannot offer the beginnings of a dialog that I cannot sustain with the facilities I have at the moment.

 

[The_Doc_Man]

Back to the issue of NULL in a field. NULL is your friend only if you expect to see your friend. If you program in such a way as to neglect how you handle NULL when you see it, you have screwed up. I can't say it plainer than that. If you program in such a way that NULL doesn't bother you because you either negate its bad effects when you see it or detect it before the bad effects can occur, you have done the right thing in your design. I can't say THAT any plainer, either.

As to what a NULL means,... if it means ANYTHING other than NOT INSTANTIATED YET, NO DATA AVAILABLE then you are misusing it. It is NOT the 257th value of a byte with 256 discrete non-null values. It is NOT the character string you wanted to see when there are no characters. It is strictly and only an indicator that something has not yet been instantiated. ANY OTHER USE OF NULL IS AN ABUSE OF ITS PROPERTIES. (And it invites trouble on conversion to other database platforms as well.)

If there is a flaw in the way Access handles NULL in a yes/no field because one was located in the non-matched portion of a record returned by an OUTER JOIN, you should take this fact into account. But to use it as an excuse to say NEVER USE YES/NO fields is silly. I showed a UNION query that would prevent this egregious behavior as an exact replacement for the outer join. Just program around the problem if it bites you. Take mitigating steps commensurate with the problem. Eliminating Yes/No fields entirely is a good example of a Procrustean solution. (Google it...) Yes/No fields are natural parts of any database design, just as are nulls.

One might as well cut off one's nose because in Spring one gets allergies that make one sneeze. It is the same concept.

 

[The_Doc_Man]

I have sent a message to Allen expressing my views.

As of the date/time of this post, I have not gotten a reply yet.