Yes/No Data Type vs. Indexed Lookup Fields

[dayna]

Here’s the deal: I’m no fan of the Yes/No data type. Not only do I dislike the checkbox/toggle display options on forms, but I often encounter situations where “N/A” or “Not Specified” or “Unknown” is a more appropriate designation than “True” or “False.” So, what I usually do is create my own custom Yes/No lookup tables to which I can link “semi-Boolean” fields in other tables. Since my previous projects have been small to medium-sized and designed for a single user, this has worked well for me in the past.

Now I have been commissioned to do a larger, multi-user db for work, and as I look at my table design, I’m wondering if this strategy would be wise given that some of my tables have upwards of 40 fields (times up to 100 records). Many of those fields are of the semi-Boolean type that I described above, and up to half of those should be indexed.

So, I have a lot of questions at this point, but my primary one here is: At what point (if any) will my avoidance of the built-in Yes/No data type come back to bite me in the ass? Would I be better off using the built-in Yes/No data type where I can and my semi-Boolean lookup when absolutely necessary, or should I strive for consistency?

It might be worth mentioning that the backend will reside on a server at work, and there will be at least two front ends. The most widely-distributed front end will be read-only, and the other, which will have editing capabilities and record-level locking, will only be used by three or four people, and likely not at the same time. Everyone has Access 2003.

Any advice, search term suggestions, recommended readings would be most appreciated. I would hate to spend a lot of time creating this only to find that the performance sucks! I am a teacher by trade, not a techie, so if I’m totally turned around on this and asking all the wrong questions, I’m not too proud to be straightened out. Thanks!

 

[Pat Hartman]

Having a user defined list of values is infinitely more flexible than boolean values so I don't see anything wrong with what you are doing. I don't like defining value lists in tables so I tend to use a table to hold the values for my combos. I have one table, two forms, and two reports that I add to most of my databases to manage my simple lookup fields. Each time I have a new set of codes to define, I add a new "table" to my table. I create a query for each "table" and that is what I use as the RowSource for the combos. The forms and reports allow the user to manage the code set. So, any code set that does not have coding dependencies is a candidate for stuffing into my common codes table.

 

[dayna]

Thanks, Pat, for the reassurance and the good ideas. As I mentioned, I've never worked on a project like this before, so I've never seen my Relationships window get so hairy. I guess that's what freaked me out.

I'm not sure what motivates people like you to share your time and expertise with jackasses like me, but I sure do appreciate your help!

 

[Pat Hartman]

I actually started doing this more than 10 years ago on another site which unfortunately no longer exists. I went searching for information and found that I knew some answers and so posted them. I kept at it because I actually still learn things from the questions and answers of others so it as not so one-sided as it seems.

 

[The_Doc_Man]

I'll take it another step along the way. I understand the "semi-boolean" concept all too well. You are right to not try to bang on a Yes/No field to make it fit. It won't. Some months ago, forum member Banana and I had a lengthy discussion about nulls as an extra state for Yes/No fields and other fields, too. I believe I convinced him that when you have at least one (and maybe more) "extra" states of the "Don't know" "Don't care" "Doesn't apply" variety, you can use a BYTE code for up to 256 possible states.

Here's the bottom line... Access doesn't tell you anything you didn't tell it first. So if you ever expect to get back "Don't know" then you need to STORE a "Don't know" somehow. And a non-Yes/No state code is the best way to do that.

As to tracking inventory, there are several types of inventory. A library inventory differs in some ways from a factory or mechanic's shop inventory, though they have many similarities, too. The best advice anyone can give you is the old carpenter's rule my father-in-law (God rest him) used to tell us... Measure twice, cut once. In Access, that means you should commit your design to a document. Then review it with other sets of eyes - of the folks who will be helping you. Discuss it before you implement it. Answer any questions that come up ... AND DOCUMENT THE QUESTIONS! Never let yourself lose an idea that emerges from one of your design or analysis meetings. Take meticulous notes. (Dare I say "Obsessive Compulsive" notes?)

When I was a design manager for a commercial shop, we built "the project bible" before we allowed anyone to write anything else except experimental code to test some theory. Then we followed that project bible as gospel truth unless we tripped over something we hadn't thought of initially. When we were finished, that project bible was oddly enough also a major element of the formal documentation of the final product.

I digressed a little, but the point is, your decision of Yes/No vs. an encoded field with more than two states is something that goes into the project bible along with the reasons for/against and the final decision on that topic.

 

[Banana]

One more thing-

I discovered that there is nothing stopping you from using negative numbers in PK for the lookup tables. I've since used that to hold together values that contains metadata (e.g. "Don't know", "Not Applicable", "Invalid", what have you). One advantage of this is that query is now easier because you only need to pass WHERE PK > 0 as the criteria to get all rows without the metadatas, rather than filtering for individual "flags" or vice versa to review rows that does not have the complete information (e.g. to push back to the users for example).

(A caveat, though- I'm very positive that once you've exhausted all positive values in PK, e.g. a long integer, it will start to use up negative values and throw wrench in the machinery. But since you'd need to have more than 2 billions rows, I decided to take that risk)

 

[gemma-the-husky]

i use yes/no's a lot - i was under the impression that a yes/no could not be null, by definiton, but i'm not so sure now

but i do find nulls an irritation rather than useful - particularly because everytime you examine anything you have to consider whether it could be null as well as a value

 

[datAdrenaline]

>> i was under the impression that a yes/no could not be null, by definiton, but i'm not so sure now <<

A Yes/No field CAN NOT hold a value of Null... if you attempt to write a Null to a Yes/No, you will get a False (No) in the table. Through an append query object you can write the range of the Integer datatype to a Yes/no and all but 0 will yeild a True in the table. Through the datasheet view of table, and the Yes/No displayed as a text box, you can input the range of a Currency datatype and all but -.5001 to .5001 will yeild a True in the table ... I presume the same phenomenon for a text box on a form.

However ... a CHECK BOX can have a value of Null ... which confuses folks because they think that if a check box can have a triple state then the Yes/No field can too ... but alas it can not!

 

[Pat Hartman]

Since Y/N can only hold -1 or 0, Jet assigns 0 as the default.

y/N should ONLY be used when a field is required and always has a value. For example. I use it for my ActiveFlg on all the entries in my lookup tables. Every row is always active or inactive so there is never a case where I would want to support a null. However, users don't understand the precision of strong typing and when they say they want a Y/N field, they almost certainly want a check box but also, there is a great likelihood that Null will be a potential initial value. An example of this would be a flag named ApprovedFlg. It should be y if approved, n if not approved, and start out as null because it has not yet been checked.

gemma, like relationships, nulls fall naturally into place as you define business rules. They are used whenever a column's value is optional. You would also use null as an initial value for certain field types which are actually required but for which you do not want to specify a default. Don't avoid them, embrace them and learn how to use them. If you think nulls are annoying with Access, you should work with nulls in a more rigid language such as COBOL. When working with relational databases, any field that was defined as not required at the table level, came with a paired flag to identify whether or not its sibling was null. So before you could reference the actual field, you needed to check the null flag to see if there was a field you could reference.

 

[ChrisO]

A Boolean field can be Null and VBA will raise an error if not handled correctly.

Option Compare Database
Option Explicit


Private Sub Form_Current()

    DoMyThing MyBoolean
    
End Sub


Sub DoMyThing(SomeBoolean As Boolean)

    MsgBox "We got here."

End Sub

A test database is attached in which the error occurs and it should be handled.

Regards,
Chris.

 

[datAdrenaline]

Hello Chris ...

I disagree ... my statement was ...

>> A Yes/No field CAN NOT hold a value of Null... if you attempt to write a Null to a Yes/No, you will get a False (No) in the table. <<

The error you present (which shows up only on a New record) either probably has to do with the Null state of the edit buffer of a NEW record (which is technically not part of the table's data) ... so ... I stand by my statement that a Yes/No field can not hold a Null value ... maybe it would be better to say ... can not STORE a Null value.

Do you agree?

 

[ChrisO]

G’day Brent.

Whatever words we wish to use we need to be aware that it can be Null and cause an error.
(I simply posted the proof, nothing more.)

Regards,
Chris.

 

[datAdrenaline]

The phenomenon you see is some "voo doo magic" (real technical term eh?) of the user interface and how it interacts with the JET table ... I make that claim because of this bit of code that you can run in your db ..

Public Sub TestNullBoolean()
    With CurrentDb.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset)
        .MoveLast
        .AddNew
        Debug.Print IsNull(!MyBoolean) & ":" & VarType(!MyBoolean) & ":" & !MyBoolean
        .CancelUpdate
    End With
End Sub

In the immediate window you will see:
False:11:False

So .. a new record is NOT null, has a type of Boolean, and a value of False

Now ... lets modify your Forms Current event ...

Private Sub Form_Current()
 
    Debug.Print IsNull(Me.MyBoolean) & ":" & VarType(Me.MyBoolean) & ":" & Nz(Me.MyBoolean, "<Null>")
 
End Sub

On existing records the immediate window shows this ...

False:2:0

WHOA! ... MyBoolean is Not null, but is an INTEGER!!, and has a value of 0 ... voo doo magic!!! ... this is not completely unreasonable .. but unexpected by me!! ... It seems the UI is doing some stuff eh?

On a New Record ... we get ...
True:1:<Null>

Which supports your point that we need to be aware of the scenario you have presented ... but ... it seems the UI is treating MyBoolean a variant ...

So ... I still contend that a Yes/No field (JET's BIT typed field) will not hold/store a Null ... but the Access UI sure does make it look like it on a new record! ...

Any further thoughts? ...

 

[ChrisO]

It’s not "voo doo magic" Brent, it’s a real error due to the Boolean being Null.

>>it seems the UI is treating MyBoolean a variant<<
That’s because it is a variant.
As far as I know, every field in a table is a variant.
Even the auto number data type can be Null.

So when it walks like a Null, talks like a Null and the compiler says it’s a Null…it’s a Null.

Please see the attached image.

Regards,
Chris.

 

[datAdrenaline]

I guess your not seeing it ... The UI CONTROL named "MyBoolean" is not the same as the FIELD named "MyBoolean" ... Access creates a hidden CONTROL named "MyBoolean" that is bound to the FIELD of MyBoolean, so the error you generate is due to the fact that you are refering to a CONTROL (which hold variants, and thus Nulls) ... NOT a Yes/No FIELD (which will not hold a Null) .... Use this code in you Form_Current() code ...

Private Sub Form_Current()
 
    'Print stats on the CONTROL
    With Me.Controls
        Debug.Print "MyBoolean CONTROL ..." & vbCrLf & _
                "   Is it Null  :" & IsNull(!MyBoolean) & vbCrLf & _
                "   Data Type   :" & VarType(!MyBoolean) & vbCrLf & _
                "   Object Type :" & TypeName(!MyBoolean) & vbCrLf & _
                "   Value       :" & Nz(!MyBoolean, "<Null>") & vbCrLf
    End With
 
    'Print stats for the FIELD
    With Me.Recordset.Fields
        Debug.Print "MyBoolean FIELD ..." & vbCrLf & _
                "   Is it Null :" & IsNull(!MyBoolean) & vbCrLf & _
                "   Data Type  :" & VarType(!MyBoolean) & vbCrLf & _
                "   Object Type:" & TypeName(!MyBoolean) & vbCrLf & _
                "   Value      :" & Nz(!MyBoolean, "<Null>")
    End With
 
End Sub

You will see that on existing records you get ...

MyBoolean CONTROL ...
   Is it Null  :False
   Data Type   :2
   Object Type :AccessField
   Value       :0
 
MyBoolean FIELD ...
   Is it Null :False
   Data Type  :11
   Object Type:Field
   Value      :False

{Note that the results will be the same if you implicitly reference the control named "MyBoolean". For example: "Me.Controls!MyBoolean" is the same as just "MyBoolean" in VBA code behind the form}

Access created the hidden control of type AccessField named MyBoolean. The data type of 2 indicates the value in the MyBoolean control is an Integer, and the value is 0. However, the FIELD named MyBoolean has an object type of Field; a data type of 11, which is Boolean, and the value is False ...

Now ... lets move to a New record on the Form, you will see the following printed to the immediate window:

MyBoolean CONTROL ...
   Is it Null  :True
   Data Type   :1
   Object Type :AccessField
   Value       :<Null>
 
MyBoolean FIELD ...
   Is it Null :False
   Data Type  :11
   Object Type:Field
   Value      :False

The CONTROL is definately Null; has a data type of 1, which translates to "Null"; and a value of Null. All this exactly as you state, but again, the Field is not being referenced ... The FIELD is NOT Null; still indicates a data type of 11, which is Boolean; and contains the value of False.

So .. has this convinced you of the veracity of my statement ...
"A Yes/No field CAN NOT hold a value of Null... "

And at least made you consider the possibility the your following statement is inaccurate? ...
"A Boolean field can be Null ..."

.....

In addition let me repeat this situation you brought to light definately needs to be dealt with if a developer finds themselves in a similar circumstance ...

 

[ChrisO]

>>So .. has this convinced you of the veracity of my statement ...
"A Yes/No field CAN NOT hold a value of Null... "

And at least made you consider the possibility the your following statement is inaccurate? ...
"A Boolean field can be Null ..."<<

Nope… It walks like a Null, it talks like a Null, the compiler says it’s a Null…it’s a Null.

It also consumes the same space as a variant (which can be Null).

Regards,
Chris.

 

[datAdrenaline]

ARGH!! ... I am NOT disputing its a Null .. but the "It" is not a Boolean Field ... its a Control ...

 

[ChrisO]

Can’t blame me if Microsoft put it in the wrong place but it is in the Field list.
(If you’ve got Bills private number I’ll give him a ring and ask him to shift it. )

 

[The_Doc_Man]

Chris, Brent... Let's see if I can confuse the issue.

What matters is that from end to end in the life cycle of the record, there is a time very early in that cycle when it CAN be null. The pitfall is not that a Yes/No field in a record can or can't be a null. The real trap is that SOMETIMES you can fooled as to its value, whatever that value happens to be.

When you are instantiating ANY DATA STRUCTURE AT ALL, there is a time before the value initialization code is complete. During that time, if you inappropriately attempt to use ANY FIELD OF ANY DATA TYPE in a partially init'd structure, you are asking for trouble and probably WILL see something null or empty somewhere that your logic didn't expect to see.

The programming principle to be observed here is less that the Yes/No field can be null; instead, it can be an unexpected value. And the trap is failing to correctly execute a complete "instantiator" on it before you try to use it.

In a recordset that is created via .NEW method, if there is no default instantiator for each field, then you run the risk of writing an abnormal record. Regardless of the format of that abnormality.

Can a "proper" yes/no field ever be null? Yes, before the first time its freshly instantiated record is first written.

 

[ChrisO]

Yep, Doc, and that’s exactly what my little demo proved.
All table fields are born equal, their variants and a variant can be null and will sometimes bite when least expected.

Regards,
Chris.