[AccessD] Very interesting quirk in table design...

Curious that in Access 97, and I'll assume subsequent versions as well, a 
yes/no field stores 2 bytes for each entry.  Using a numeric value like a 
byte requires half the storage and gives you 8 states that equate to 8 two 
state fields or 4 four state fields or whatever other combination of 8 bits 
you devise, plus null.  I've never understood why the yes/no field requires 
two bytes and use bytes for boolean fields for the increased flexibility.  
Form logic doesn't seem to require any change if you simply use one byte 
field for each field requiring a boolean display.

Hen


>From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
>Reply-To: accessd at databaseadvisors.com
>To: Drew Wutka <DWUTKA at marlow.com>, accessd at databaseadvisors.com
>Subject: Re: [AccessD] Very interesting quirk in table design...
>Date: Wed, 14 May 2003 11:12:46 +1000
>
>On 13 May 2003 at 19:42, Drew Wutka wrote:
> >
> > So I hypothesized that by setting a Yes/No field, you had either 0 or
> > -1 (maybe 1, I don't remember), but since a Yes/No field only has two
> > possible values, there was nothing for null, so each field is
> > automatically populated with 0 or -1.  By switching that field to a
> > number field, it tried to 'convert' the data in those fields, which it
> > did successfully to 0.
> >
> > So to prove this, I went into design for that table, changed my 'null
> > filled' number field to Yes/No, hit save, changed it back to number,
> > and hit save again.  Whalla, went into datasheet view, and no I had
> > all zero's in my number field.
> >
> > Pretty interesting eh?  (Or do I just need some sleep?)
> >
>
>A Yes/No field can only have  two values Yes or No, not 3 values
>(Yes, No, Null/Unknown)
>
>That's why you get the annoying behaviour of multiple bound Yes/No
>fields on a form (assuming your form is bound <g>).
>
>Set the field Required Property to "NO" and the default value to
>"NULL"  and on your form, set the Checkboxes to TripleState.
>
>When you go to a New record, the check boxes are grey (Null), but as
>soon as you start to enter any data, they ALL become False (ie
>unticked). When you try to cycle through the values of one of
>checkbox with the space bar or mouse, you get an error when you try
>to set it to Null ("The value you enterd isn't valid for this field")
>
>The only way to have a TripleState field with a checkbox is by making
>the field type numeric :-(
>
>
>
>
>
>
>
>--
>Lexacorp Ltd
>http://www.lexacorp.com.pg
>Information Technology Consultancy, Software Development,System
>Support.

_________________________________________________________________
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*   
http://join.msn.com/?page=features/junkmail