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!

 

[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!

 

[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!

 

[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.

 

[datAdrenaline]

If all table fields were variants, then the table size would NOT change when you change the datatypes of fields in the table ...

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

Nope ... did you see my recordset code earlier in the thread (Post #13)? ... after the .AddNew (ie: a newly instantiated, uncommitted record) a Yes/No field is False ... even if a default value is not specified ...

 

[ChrisO]

Attached are two databases: -

Both have one table of 131069 records.
One is of type Date/Time
The other is of type Boolean

Both databases are 2188KB in size.

 

[datAdrenaline]

Hello Chris,

One field does not cut it because of data structures and I don't know what else, but one field does not reveal the difference .... so try this ...

Same two data bases with 8 fields ...

The Date/Times: 9,220,096 bytes
The Booleans: 2,240,512 bytes

Definately of different size ... so its doubtful all fields are Variants as we know them in VBA.

-----

Do the code samples I have provided through out this thread (escpecially in post #15) not verify the claims I have made? ... Does my code not verify that in your samples, you were working with a CONTROL, which can indeed be Null, as appossed to a Yes/No FIELD which can not be Null? ... The code verifies that when working through the Form object in the manner you have, you're working with a CONTROL and not directly with the FIELD. Its simple really, a Form object does not even have a Fields collection, but it has a Controls collection...

Also ... you state ...

>> All table fields are born equal, their variants and a variant can be null and will sometimes bite when least expected <<

All table fields EXCEPT booleans, do seem to be born equal (but not saved equal ) ... in the Edit Buffer (Copy Buffer, New Record Buffer ... whatever..) all field types (except boolean) seem to be of some of Variant persuassion at this stage simply because they CAN hold a Null (I actually view that as specialized variants simply because they can not take on any form, they can take on only one form...that of the data type) ... but .. the boolean is different ...

Public Sub SimpleTest()
    With CurrentDb.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset)
        .MoveLast
        .AddNew
        Debug.Print "Boolean: " & IsNull(!MyBoolean) & ":" & VarType(!MyBoolean) & ":" & Nz(!MyBoolean, "<Null>")
        Debug.Print "Integer: " & IsNull(!MyInteger) & ":" & VarType(!MyInteger) & ":" & Nz(!MyInteger, "<Null>")
        Debug.Print "Date   : " & IsNull(!MyDate) & ":" & VarType(!MyDate) & ":" & Nz(!MyDate, "<Null>")
        Debug.Print "Byte   : " & IsNull(!MyByte) & ":" & VarType(!MyByte) & ":" & Nz(!MyByte, "<Null>")
        Debug.Print "Text   : " & IsNull(!MyText) & ":" & VarType(!MyText) & ":" & Nz(!MyText, "<Null>")
        Debug.Print "Memo   : " & IsNull(!MyMemo) & ":" & VarType(!MyMemo) & ":" & Nz(!MyMemo, "<Null>")
        .CancelUpdate
    End With
End Sub

{Note: all Access generated default values were removed ... ie: the default 0 for Numeric fields}

The above produces this in the immediate window ...

Boolean: False:11:False
Integer: True:1:<Null>
Date   : True:1:<Null>
Byte   : True:1:<Null>
Text   : True:1:<Null>
Memo   : True:1:<Null>

So ... all but a boolean field was intantiated as a Null ...

.......

Along with all, this I wholeheartedly agree that, as Doc says ... you will see something null or empty somewhere that your logic didn't expect to see. The end of post #15 alludes to that as well.

If my arguments have not defended my claims (that a Yes/No FIELD can not be Null ... and your sample in db8 was using a CONTROL), please point out the shortcomings in my presentations. I highly respect your abilities in general, as well as your ability to ferret out issues and such as that.

.
.
.

By the way ... Bill's number is ...

866-366-6445
VOO DOO MGIK ...