By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 2,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

A97 table field of Yes/No type without a default value property setting has a default value?

P: n/a
MLH
I have an A97 table with a Yes/No field named
TowJob and a form bound to that table. The
TowJob control on the form is bound to the same
field. It is an option group with Yes and No bttns
valued at -1 and 0 respectively.

Unless I specifically set the table field's defaultvalue
to Null, the form comes up with an automatic value
of 0 in the control? The control has NO default
value property setting. Why is that?

Why must one EXPLICITYLY set a table field's
defaultvalue property to Null instead of simply entering
nothing at all into the defaultvalue property setting field?
Nov 13 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
MLH wrote:
Why must one EXPLICITYLY set a table field's
defaultvalue property to Null instead of simply entering
nothing at all into the defaultvalue property setting field?


I'm not sure, but that's the way the Yes/No field type works in Access.

On a related subject, it sounds as if you might be relying on Null as a
value so that you'd have three values for a Yes/No field. It's
generally considered not a good idea to do this sort of thing. In fact,
strict database design theorists will tell you no field in a record
should ever be null (thoughts on this concept and actual practice do
differ, of course). Nulls aren't included in indexes and null does not
equal null.

My approach to this sort of thing is to use a numeric field and limit
the values to 0, 1, 2 or 1, 2, 3 or something. You might find this a
better way to do things and restrict the use of Yes/No fields to dtabase
entities for which there are only two states.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #2

P: n/a
MLH
I see your point. I just see it a bit differently perhaps. I believe
null values serve a useful purpose. It is OK to have no data
in a record that doesn't exist, right. A record that won't be
written say, until my son graduates, is a perfect example of
a record that doesn't exist. Whether its a record in a high
school filing cabinet maintained on paper or a record in
a database, we can talk about it. We can call it "a record
that hasn't been written yet" if we want. That's somewhat
verbose. Its OK to use a word like Null to refer to such
a record. Likewise, fields in a record that Suzie Secretary
hasn't fully completed yet are OK to be that way. Just
give Suzie some more time and they'll be complete.
Meanwhile, can we talk about values in the fields that
aren't there yet? Sure we can. Must we always refer
to them though as "values that aren't there yet"? I
don't think so. We can agree to use the word Null to
refer to values that aren't there yet. If I ask you for a
value in such a field, I'll accept either response from
you and I'll know what you mean. Personally, I prefer
Null as the answer. I don't really like the idea of putting
a space, or dbl-quotes, or anything else at all to represent
nothing when NOTHING itself does quite a good job of
it. If a Yes/No field hasn't been completed with a value
yet, it makes perfect sense to me to say just that. It doesn't
make sense to me to put SOMEthing in there when NOthing
does the job - just fine. Nulls make sense to me.

A Yes/No field named [HaveTheHorsesBeenFed] could contain
Null if "What horses?" is the answer you get, or it could be Yes
or No, depending on what the stable boy says.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Wed, 15 Jun 2005 11:26:25 -0230, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote:
MLH wrote:
Why must one EXPLICITYLY set a table field's
defaultvalue property to Null instead of simply entering
nothing at all into the defaultvalue property setting field?


I'm not sure, but that's the way the Yes/No field type works in Access.

On a related subject, it sounds as if you might be relying on Null as a
value so that you'd have three values for a Yes/No field. It's
generally considered not a good idea to do this sort of thing. In fact,
strict database design theorists will tell you no field in a record
should ever be null (thoughts on this concept and actual practice do
differ, of course). Nulls aren't included in indexes and null does not
equal null.

My approach to this sort of thing is to use a numeric field and limit
the values to 0, 1, 2 or 1, 2, 3 or something. You might find this a
better way to do things and restrict the use of Yes/No fields to dtabase
entities for which there are only two states.


Nov 13 '05 #3

P: n/a
"MLH" <CR**@NorthState.net> wrote in message
news:4i********************************@4ax.com...
I see your point. I just see it a bit differently perhaps. I believe
null values serve a useful purpose. It is OK to have no data
in a record that doesn't exist, right. A record that won't be
written say, until my son graduates, is a perfect example of
a record that doesn't exist. Whether its a record in a high
school filing cabinet maintained on paper or a record in
a database, we can talk about it. We can call it "a record
that hasn't been written yet" if we want. That's somewhat
verbose. Its OK to use a word like Null to refer to such
a record. Likewise, fields in a record that Suzie Secretary
hasn't fully completed yet are OK to be that way. Just
give Suzie some more time and they'll be complete.
Meanwhile, can we talk about values in the fields that
aren't there yet? Sure we can. Must we always refer
to them though as "values that aren't there yet"? I
don't think so. We can agree to use the word Null to
refer to values that aren't there yet. If I ask you for a
value in such a field, I'll accept either response from
you and I'll know what you mean. Personally, I prefer
Null as the answer. I don't really like the idea of putting
a space, or dbl-quotes, or anything else at all to represent
nothing when NOTHING itself does quite a good job of
it. If a Yes/No field hasn't been completed with a value
yet, it makes perfect sense to me to say just that. It doesn't
make sense to me to put SOMEthing in there when NOthing
does the job - just fine. Nulls make sense to me.
It's not as simple as that.

A 'null record' as you describe it looks meaningless to me. It there isn't a
row in a table there isn't a row in a table. That's not a null anything,
just a non-existant thing. And with all due respect looks like an attempt to
'justify' null _fields_ with a specious comparison to records that don't
exist.

A field in a record with a null value is different. The reason that they are
treated suspiciously is that the actual meaning is ambiguous.

Does null mean that no value can exist for that field, or that the value is
unknown?
A Yes/No field named [HaveTheHorsesBeenFed] could contain
Null if "What horses?" is the answer you get, or it could be Yes
or No, depending on what the stable boy says.
Sure. But shouldn't the database be supplying answers, not asking questions?

Practial Issues in Database Management by Fabian Pascal (Addison Wesley,
ISBN 0-201-48555-9) has a good chapter on this (chapter 10).

Nulls are a problem in relational database theory. There's no getting round
that fact. What was 2 valued logic now becomes 4 valued logic -
yes/no/missing/unknown.

Do I have fields which are allowed null in my databases? Yes. Has that
always been the best design choice? No. The alternative, a lot of the time
is to spin off the nullable part of tables into seperate tables. But this
makes the structure more, and sometimes a lot more, complex. And in a nice
DBMS like Access, with it's bound forms and suchlike, this can make
development substantially harder.

At this point somebody's going to start on about 'relational database
theorists' and all that. Access is, supposedly, a relational database tool.
We ignore the theory at our peril. I have never read anything by the
'theorists' that hasn't made complete sense. Many human activities have a
'theoretical' basis. Doesn't mean we can always implement the theory. But
looking hard at the theory, especially as expounded by Pascal and CJ Date
reminds us, again and again that the database should be about _meaning_.
That it's foundation in first order predicate logic forces us to think about
what the data actually mean.

So what does your null value in your boolean HaveTheHorsesBeenFed field
mean. They haven't been fed? Horses, what horses? We don't have any horses?
The stable boys have come back from the stables and are on a work to rule so
won't tell us whether they've been fed or not? They'll get fed when I'm good
and ready?

Mike

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Wed, 15 Jun 2005 11:26:25 -0230, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote:
MLH wrote:
Why must one EXPLICITYLY set a table field's
defaultvalue property to Null instead of simply entering
nothing at all into the defaultvalue property setting field?


I'm not sure, but that's the way the Yes/No field type works in Access.

On a related subject, it sounds as if you might be relying on Null as a
value so that you'd have three values for a Yes/No field. It's
generally considered not a good idea to do this sort of thing. In fact,
strict database design theorists will tell you no field in a record
should ever be null (thoughts on this concept and actual practice do
differ, of course). Nulls aren't included in indexes and null does not
equal null.

My approach to this sort of thing is to use a numeric field and limit
the values to 0, 1, 2 or 1, 2, 3 or something. You might find this a
better way to do things and restrict the use of Yes/No fields to dtabase
entities for which there are only two states.

Nov 13 '05 #4

P: n/a
MLH
Semantics, Mike. What's the point?

Nov 13 '05 #5

P: n/a
Yes

"MLH" <CR**@NorthState.net> wrote in message
news:d3********************************@4ax.com...
Semantics, Mike. What's the point?

Nov 13 '05 #6

P: n/a
On Mon, 20 Jun 2005 06:56:43 +0100, "Mike MacSween"
<mi***************************@btinternet.com> wrote:
Yes

"MLH" <CR**@NorthState.net> wrote in message
news:d3********************************@4ax.com.. .
Semantics, Mike. What's the point?

If a control only has one of two values, how is it possible to not have a
default value? Maybe a control with three values is what is needed. Yes,
Maybe, No.

Chuck
Nov 13 '05 #7

P: n/a

"MLH" <CR**@NorthState.net> wrote in message
news:4i********************************@4ax.com...
I see your point. I just see it a bit differently perhaps. I believe
null values serve a useful purpose.
If it is clear what null means. Your 'semantics.' Problem is, we can't tell
what null means. It's an absence of value.
It is OK to have no data
in a record that doesn't exist, right. A record that won't be
written say, until my son graduates, is a perfect example of
a record that doesn't exist.
Er, OK. There are lots of things that don't exist. Now what's your point?
Whether its a record in a high
school filing cabinet maintained on paper or a record in
a database, we can talk about it. We can call it "a record
that hasn't been written yet" if we want. That's somewhat
verbose.
Certainly is. And a bit weird too, if you ask me.
Its OK to use a word like Null to refer to such
a record.
No it's not.
Likewise, fields in a record that Suzie Secretary
hasn't fully completed yet are OK to be that way.
That's a comparison that doesn't apply. These non-existent records (your
null records) aren't the same as null values in fields in real actual
records. We can count null field, but how many records-that-don't-exist-yet
have you got in your database? 10, 10 ^10000 or just good old infinity?
Just
give Suzie some more time and they'll be complete.
Meanwhile, can we talk about values in the fields that
aren't there yet? Sure we can. Must we always refer
to them though as "values that aren't there yet"? I
don't think so. We can agree to use the word Null to
refer to values that aren't there yet.
What about values that won't EVER be there because Jo Bloggs doesn't have a
middle name, or is a UK citizen so doesn't have a Social Security number, or
is a man so doesn't have an appropriate value for gave birth on (what a crap
example!).
If I ask you for a
value in such a field, I'll accept either response from
you and I'll know what you mean.
Really? You think so.
Personally, I prefer
Null as the answer. I don't really like the idea of putting
a space, or dbl-quotes, or anything else at all to represent
nothing when NOTHING itself does quite a good job of
it. If a Yes/No field hasn't been completed with a value
yet, it makes perfect sense to me to say just that. It doesn't
make sense to me to put SOMEthing in there when NOthing
does the job - just fine. Nulls make sense to me.


Good. Then you use them.

Semantics is right. This stuff is to do with the MEANING of data. If you
aren't prepared to at least attempt to tackle the problem of nulls, that's
your problem. But it's a problem you'll have to deal with.

In fact, hey look, you're having to deal with it right now, because, guess
what, Access considers a Yes/No field to have only 2 values and you want 3,
Yes/No/Neither. Or something. QED

Mike

Nov 13 '05 #8

P: n/a
"MLH" <CR**@NorthState.net> wrote in message
news:4i********************************@4ax.com...
I see your point. I just see it a bit differently perhaps. I believe
null values serve a useful purpose. It is OK to have no data
in a record that doesn't exist, right. A record that won't be
written say, until my son graduates, is a perfect example of
a record that doesn't exist.


What if your son _doesn't_ graduate? Let me guess, the non existent record
will be deleted?

You know, I've got lots of records that haven't been filled in waiting for
me to do the thing that's needed to get them filled in.

DVLA is just waiting for me to pass my Class 1 HGV test, then they can fill
that record in.
The Church of England is waiting to fill in the records about my appointment
as a priest.
Some where there's a record for a black belt at origami just waiting for my
details to go in it.

That's when I get round to all of those.

I know I'm being a bit hard about this, but it's important. And it's
something you see over and again in newsgroups and in applications. Failure
to think hard about what data stored in databases mean, especially when it
comes to 'missing' data.

Mike
Nov 13 '05 #9

P: n/a
MLH <CR**@NorthState.net> wrote in
news:4i********************************@4ax.com:
A Yes/No field named [HaveTheHorsesBeenFed] could contain
Null if "What horses?" is the answer you get, or it could be Yes
or No, depending on what the stable boy says.


If you need to track "not answered yet" vs. "has an answer of Yes or
No" then you need to alter the data structure. You have two choices:

1. don't use a Boolean field, but instead an Integer field that can
be Null, -1 or 0 (using a field-level validation rule). You can then
use a 3-state checkbox control on your forms to edit that field.

2. put the Boolean fields in a separate table, with 3 columns:

ParentID VariableType Value

The ParentID points to the parent record. The VariableType indicates
which question is being answered. The Value would be Boolean, with
Yes/No.

"Not answered yet" can be determined by checking the recordcount of
the child table for the particular VariableType (i.e., the question)
for the particular parent record.

Any table with lots of Boolean fields in it is probably denormalized
to some degree. Any questionaire-type application will use a
structure with narrow tables of a few fields, one for each question,
connected to a record identifying the person whose answers those
are, rather than a big wide record for each person completing the
questionaire. That structure has another huge advantage: when the
questions change you don't have to change the structure of the table
holding the answers -- you just add records to the table describing
the questions.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #10

P: n/a
MLH
I have to agree that if someone asks me,
"Do you have any money in the bank?"
and I provide one of three answers:
Yes / No / Dunno
that is better than simply not answering
- akin to a null response.

I guess I've had to deal with nulls as a matter
of reality for so long - I've learned to live with
them. Maybe 3 cases are best:
Yes / No / Dunno. I'll certainly have to drop
the Yes/No data type on the field. Suppose
I could do so & make the default answer
Dunno. User would have to explicitly answer
Yes or No to relieve the ambiguity of a Dunno
value.
Nov 13 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.