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?
10  2702 
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
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.
"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.
Semantics, Mike. What's the point?
Yes
"MLH" <CR**@NorthState.net> wrote in message
news:d3********************************@4ax.com... Semantics, Mike. What's the point?
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
"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
"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
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
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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: kie |
last post by:
hello,
i have a table that creates and deletes rows dynamically using
createElement, appendChild, removeChild.
when i have added the required amount of rows and input my data, i
would like to...
|
by: NullBock |
last post by:
Hi,
I'm trying to create a form with a text-input (not textarea) that
expands to the size available, using CSS. I thought that a simple
display:block would work:
<form>
<div>
<input...
|
by: David Deacon |
last post by:
Hi i was given the following advise,below my OriginalQuestion
I am a little new to ADOX can you direct me to the following
Do i place the code behind a button on a form?
Or do i place it in the...
|
by: Lee C. |
last post by:
I'm finding this to be extremely difficult to set up. I understand
that Access won't manage the primary key and the cascade updates for a
table. Fine. I tried changing the PK type to number and...
|
by: Kathy Krizl |
last post by:
I'm probably doing something stupid, but I have a make table query.
One of the tables I reference has some check box fields in it. Their
Data Type is Yes/No, their field property format is Yes/No,...
|
by: deko |
last post by:
I've heard it's best not to have any formatting specified for Table fields
(except perhaps Currency), and instead set the formatting in the Form or
Report. But what about Yes/No fields? When I...
|
by: Art |
last post by:
Hi everyone
I was hoping someone might be able to help me with this. I'm just starting to try to work with MS Access tables through VB.net. In Access I can take an existing table and add a new...
|
by: mukesh |
last post by:
Can we use expression in default value for a table field for example –
IIf(Table-1.field-1=table-2 . field-1, table-1.field-2, 0)
Interpretation – If field-1 of table -1 is like/equal to...
|
by: gweasel |
last post by:
What is the best way to apply a Validation Rule - or rather, where is
the best place to put it? Is there an advantage to putting it on the
field in the table vs setting the validation rule on the...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
