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

Dan Waters dwaters at usinternet.com
Tue May 13 21:30:37 CDT 2003


Drew,

Your life has been pretty unstable as of late, what with the fire, crashing
machines, unreliable ISPs, broken websites, etc.  We've all had these times
in our lives . . . And yes, the smallest things can seem wondrous for the
moment.

But you're right, a good night's sleep and all will be good again!  Don't
you agree?  -1, or 0?


Dan Waters

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Tuesday, May 13, 2003 6:43 PM
To: 'AccessD at databaseadvisors.com'
Subject: [AccessD] Very interesting quirk in table design...


I just noticed something tonight.  I had to go into an existing table and
add a few fields.  One field was numeric, then I was going to add two Yes/No
fields.  I did this.  The numeric field was blank/null for all existing
records.  However, I wanted a Yes/No/Not Answered for the Yes/No fields, so
I dropped one of them, and changed the other one to a byte number (1-yes,
2-no, 4-Yes, 8-No) which will let me know the answer for both and whether
they were answered at all.  When I looked back at the datasheet view, the
byte field (which was a yes/no) was all 0's, and the number field was still
all blank.  

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?)

Drew
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list