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  2759 
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 [HaveTheHorsesBe enFed] 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.
xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxx
On Wed, 15 Jun 2005 11:26:25 -0230, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtherium> 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**@NorthStat e.net> wrote in message
news:4i******** *************** *********@4ax.c om... 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 [HaveTheHorsesBe enFed] 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 HaveTheHorsesBe enFed 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
xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxx
On Wed, 15 Jun 2005 11:26:25 -0230, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtherium> 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**@NorthStat e.net> wrote in message
news:d3******** *************** *********@4ax.c om... Semantics, Mike. What's the point?
On Mon, 20 Jun 2005 06:56:43 +0100, "Mike MacSween"
<mi************ *************** @btinternet.com > wrote: Yes
"MLH" <CR**@NorthStat e.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**@NorthStat e.net> wrote in message
news:4i******** *************** *********@4ax.c om... 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**@NorthStat e.net> wrote in message
news:4i******** *************** *********@4ax.c om... 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**@NorthStat e.net> wrote in
news:4i******** *************** *********@4ax.c om: A Yes/No field named [HaveTheHorsesBe enFed] 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 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 calculate the totals in each row.
when i try however, i receive the error:
"Error: 'elements' is null or not an object"
|
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 type="text" style="display: block">
</div>
|
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 On Open event and have a the default 10% and
give the user the option to change it to 0%
I have referenced th appropriate library and the default value of the
field to change is 0.1
|
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 setting
default value to a UDF that manages the auto-numbering. Access won't
take a UDF as a default value. Okay, I'll use SQL WITHOUT any
aggregate functions, for the default value. Access won't do that
either. Okay, I create a second...
|
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, they have
no default value, and their Display Control is Check Box. When I run
my make table query, the new fields have a Yes/No Data Type, but the
field property format comes up blank, and the Display Control comes up
as Text Box. How come...
| | |
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 create a Yes/No field in a
Table the default format is "Yes/No". If I clear or change the Format
property in the General Tab (and keep the default "Checkbox" in the Lookup
tab), the field still looks the same when I open the table in Datasheet...
|
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 field with the type AutoNumber. I can then set this up as a key. Then if I go into the table I will see sequential numbers have been inserted into that field for me. Can I do this through VB.net
I'd appreciate any help or alternative suggestions...
|
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 field-2 of table-2 then value of field-1 of table-1 will be the default value other wise 0 will be the default value
Can I use this type of conditional default value if yes please tell me how. I tried to set this expression in default property of a...
|
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 form the
control is on?
Basically I have a number of controls in a form that are required, and
to check it I am setting the Validation Rule to "<>"IsNull" so that
when the user tries to tab through/click out of a required area
without entering...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
