how to enter NA for missing data

[Mr.Tom.Willems]

Hello people,

I am ussing an MS access database to enter and manage data from lab
tests.
until now i was the only one handeling the data so i had no need for a
controle on how missing data was entered, since i did it myself i knew
exactly what data was missing...

The problem is that i can have data that hasn't been enterd yet, or
data that is below detection level(so missing).
All the data fields are specified to numeric, so having the person
enter " NA " returns an error message.
Until now, i just ran a query after making sure that all data was
entered, the query replaced the blancs with "NA's". But now there or
several people working on the database, not all as skilled with
computers, so i wich to insure the integrity of the entered data.

Now is was trying a validation rule but i don't seem to understand the
principels
" iif( IsNumeric([value])=TRUE; [value];"NA") ", does not work at
all.
the function IIf and IsNumeric don't exist in validation rules.

Does anybody know how i can make sure that the columns for the
variables give me eigther a number or an "NA" or nothing when it isn't
entered.

Kind regards,
Tom.

[Daveo]

On Aug 20, 1:15 pm, [e-mail] wrote:

Hello people,

I am ussing an MS access database to enter and manage data from lab
tests.
until now i was the only one handeling the data so i had no need for a
controle on how missing data was entered, since i did it myself i knew
exactly what data was missing...

The problem is that i can have data that hasn't been enterd yet, or
data that is below detection level(so missing).
All the data fields are specified to numeric, so having the person
enter " NA " returns an error message.
Until now, i just ran a query after making sure that all data was
entered, the query replaced the blancs with "NA's". But now there or
several people working on the database, not all as skilled with
computers, so i wich to insure the integrity of the entered data.

Now is was trying a validation rule but i don't seem to understand the
principels
" iif( IsNumeric([value])=TRUE; [value];"NA") ", does not work at
all.
the function IIf and IsNumeric don't exist in validation rules.

Does anybody know how i can make sure that the columns for the
variables give me eigther a number or an "NA" or nothing when it isn't
entered.

Kind regards,
Tom.

Hi Tom,

Not sure if this solves your problem or what number ranges the data
you enter falls within, but we usually use "-99" to indicate data is
missing for a particular field.

Cheers - David

[John Winterbottom]

Consider using two columns to store your data; a numeric column, which
you have now, to represent a numeric result, (e.g. 1.234567), and a
textual representation of the numeric value, (e.g. 1.23 ppm). The
textual representation is formatted to display the appropriate number of
significant figures. In the case of a result that is below detection
users can then enter '< DL' or '< 1' etc into the text column, leaving
the numeric column as null. This also allows you to capture results that
are not numeric, (e.g. 'positive', 'negative' etc).

De-normalization comes at a price but in this case it is probably one
worth paying.

Mr************@gmail.com wrote:

Hello people,

I am ussing an MS access database to enter and manage data from lab
tests.
until now i was the only one handeling the data so i had no need for a
controle on how missing data was entered, since i did it myself i knew
exactly what data was missing...

The problem is that i can have data that hasn't been enterd yet, or
data that is below detection level(so missing).
All the data fields are specified to numeric, so having the person
enter " NA " returns an error message.
Until now, i just ran a query after making sure that all data was
entered, the query replaced the blancs with "NA's". But now there or
several people working on the database, not all as skilled with
computers, so i wich to insure the integrity of the entered data.

Now is was trying a validation rule but i don't seem to understand the
principels
" iif( IsNumeric([value])=TRUE; [value];"NA") ", does not work at
all.
the function IIf and IsNumeric don't exist in validation rules.

Does anybody know how i can make sure that the columns for the
variables give me eigther a number or an "NA" or nothing when it isn't
entered.

Kind regards,
Tom.

[Tom van Stiphout]

On Mon, 20 Aug 2007 05:15:32 -0700, Mr************@gmail.com wrote:

I don't like Daveo's suggestion of using a "magic value".
Rather I would say: null represents data that hasn't been entered yet.
A value like "<0.005" is appropriate when the test was conducted and
the value was below the detection level. Of course this requires a
text field. You can even have the computer enter this automatically if
you have a table of analytes and their detection level.

So the validation rule remains simple: it's a number or it's "<" plus
a number.

-Tom.

>Hello people,

I am ussing an MS access database to enter and manage data from lab
tests.
until now i was the only one handeling the data so i had no need for a
controle on how missing data was entered, since i did it myself i knew
exactly what data was missing...

The problem is that i can have data that hasn't been enterd yet, or
data that is below detection level(so missing).
All the data fields are specified to numeric, so having the person
enter " NA " returns an error message.
Until now, i just ran a query after making sure that all data was
entered, the query replaced the blancs with "NA's". But now there or
several people working on the database, not all as skilled with
computers, so i wich to insure the integrity of the entered data.

Now is was trying a validation rule but i don't seem to understand the
principels
" iif( IsNumeric([value])=TRUE; [value];"NA") ", does not work at
all.
the function IIf and IsNumeric don't exist in validation rules.

Does anybody know how i can make sure that the columns for the
variables give me eigther a number or an "NA" or nothing when it isn't
entered.

Kind regards,
Tom.

[DavidB]

I agree with Daveo. You should have a set value meaning that no data
has been enetered yet. In all of my Date type fields I use the date
01/01/1901 as the default and thus can search on it for dates that
have not been entered yet. Select a value for yor default that is
something that would not be a valid entry for the field in your day to
day use.

[paii, Ron]

<Mr************@gmail.comwrote in message
news:11*********************@a39g2000hsc.googlegro ups.com...

Hello people,

I am ussing an MS access database to enter and manage data from lab
tests.
until now i was the only one handeling the data so i had no need for a
controle on how missing data was entered, since i did it myself i knew
exactly what data was missing...

The problem is that i can have data that hasn't been enterd yet, or
data that is below detection level(so missing).
All the data fields are specified to numeric, so having the person
enter " NA " returns an error message.
Until now, i just ran a query after making sure that all data was
entered, the query replaced the blancs with "NA's". But now there or
several people working on the database, not all as skilled with
computers, so i wich to insure the integrity of the entered data.

Now is was trying a validation rule but i don't seem to understand the
principels
" iif( IsNumeric([value])=TRUE; [value];"NA") ", does not work at
all.
the function IIf and IsNumeric don't exist in validation rules.

Does anybody know how i can make sure that the columns for the
variables give me eigther a number or an "NA" or nothing when it isn't
entered.

Kind regards,
Tom.

Why not default to NULL to represent missing data? Your reports and queries
then can use NZ([Fieldname],"N/A") to show N/A when nothing has been
entered.

[Larry Linson]

"DavidB" <je***@yahoo.comwrote in message
news:11**********************@d55g2000hsg.googlegr oups.com...

>I agree with Daveo. You should have a set value meaning that no data
has been enetered yet. In all of my Date type fields I use the date
01/01/1901 as the default and thus can search on it for dates that
have not been entered yet. Select a value for yor default that is
something that would not be a valid entry for the field in your day to
day use.

Why wouldn't the absence of a value, that is, "Null", be an appropriate
indication? That's exactly what "null" means in Access/Jet terms. If you
want to use it in a variable, then define the variable as "Variant" type,
but any field can be null in a Record -- if nothing has been entered.

While I don't know your design and implementation, I get the strong
impression with your worrying about other users that you are allowing them
direct access to tables and queries, instead of the "safer" method of
creating a fully-developed application that enforces some control over what
they do and see by limiting their access to Forms and Reports. If my guess
is correct, then perhaps that is an approach you should consider.

Larry Linson
Microsoft Access MVP

[DavidB]

On Aug 20, 1:49 pm, "Larry Linson" <[e-mail]:

"DavidB" <[e-mail] in message

news:11**********************@d55g2000hsg.googlegr oups.com...

I agree with Daveo. You should have a set value meaning that no data
has been enetered yet. In all of my Date type fields I use the date
01/01/1901 as the default and thus can search on it for dates that
have not been entered yet. Select a value for yor default that is
something that would not be a valid entry for the field in your day to
day use.

Why wouldn't the absence of a value, that is, "Null", be an appropriate
indication? That's exactly what "null" means in Access/Jet terms. If you
want to use it in a variable, then define the variable as "Variant" type,
but any field can be null in a Record -- if nothing has been entered.

While I don't know your design and implementation, I get the strong
impression with your worrying about other users that you are allowing them
direct access to tables and queries, instead of the "safer" method of
creating a fully-developed application that enforces some control over what
they do and see by limiting their access to Forms and Reports. If my guess
is correct, then perhaps that is an approach you should consider.

Larry Linson
Microsoft Access MVP

IMHO especially in my example of dates, there should never be a null
date. If you are doing any sort of date comparisions, calulations,
etc, allowing nulls only muddies the waters. With strings, it is not
that difficult to have the string " " entered which LOOKS LIKE it is
null but in all actuality isn't.

[David W. Fenton]

DavidB <je***@yahoo.comwrote in
news:11**********************@57g2000hsv.googlegro ups.com:

IMHO especially in my example of dates, there should never be a
null date. If you are doing any sort of date comparisions,
calulations, etc, allowing nulls only muddies the waters. With
strings, it is not that difficult to have the string " " entered
which LOOKS LIKE it is null but in all actuality isn't.

Having a special value for dates that meant "not filled out" was one
of the big Y2K challenges (09/09/99).

Dates are the absolute *worst* example for you to have chosen, as a
field of date type can hold no "magic values" -- only valid dates.
And that means that you would be using a real date to pretend to
*not* be a date (which is why Sept. 9th, 1999 was a problem).

Null is the *perfect* value for a non-entered date.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Larry Linson]

"DavidB" <je***@yahoo.comwrote

IMHO especially in my example of dates, there should never be a null
date. If you are doing any sort of date comparisions, calulations,
etc, allowing nulls only muddies the waters. With strings, it is not
that difficult to have the string " " entered which LOOKS LIKE it is
null but in all actuality isn't.

No matter what you use to represent the fact, a date that has not been
entered is not a valid date, and should not be used in comparisons,
calculations, etc.. Using null does not "muddy the water", it proliferates
through the calculations as "null", which, if you like, you can format as
"NA" when you present it to the user.

Larry Linson
Microsoft Access MVP

[DavidB]

On Aug 20, 11:39 pm, "Larry Linson" <[e-mail]:

"DavidB" <[e-mail]

IMHO especially in my example of dates, there should never be a null
date. If you are doing any sort of date comparisions, calulations,
etc, allowing nulls only muddies the waters. With strings, it is not
that difficult to have the string " " entered which LOOKS LIKE it is
null but in all actuality isn't.

No matter what you use to represent the fact, a date that has not been
entered is not a valid date, and should not be used in comparisons,
calculations, etc.. Using null does not "muddy the water", it proliferates
through the calculations as "null", which, if you like, you can format as
"NA" when you present it to the user.

Larry Linson
Microsoft Access MVP

Whether the 'nothing' data is an actual date or null is irrelevant in
your case. You can represent any set value as NA in output be it null
or #1/1/1901#. However if you are wriing a query on a date field, if
you know none of the values are null, thats one less degree of
complexity that you have to cdoe for. So indeed having null does
muddy the code. The fact remains in string fields, somehting can LOOK
null and not be so. Its all a matter of personal choice. I prefer to
not have fields empty.

[paii, Ron]

"DavidB" <je***@yahoo.comwrote in message
news:11**********************@57g2000hsv.googlegro ups.com...

On Aug 20, 11:39 pm, "Larry Linson" <[e-mail]:

"DavidB" <[e-mail]

IMHO especially in my example of dates, there should never be a null
date. If you are doing any sort of date comparisions, calulations,
etc, allowing nulls only muddies the waters. With strings, it is not
that difficult to have the string " " entered which LOOKS LIKE it is
null but in all actuality isn't.

No matter what you use to represent the fact, a date that has not been
entered is not a valid date, and should not be used in comparisons,
calculations, etc.. Using null does not "muddy the water", it

proliferates

through the calculations as "null", which, if you like, you can format

as

"NA" when you present it to the user.

Larry Linson
Microsoft Access MVP

Whether the 'nothing' data is an actual date or null is irrelevant in
your case. You can represent any set value as NA in output be it null
or #1/1/1901#. However if you are wriing a query on a date field, if
you know none of the values are null, thats one less degree of
complexity that you have to cdoe for. So indeed having null does
muddy the code. The fact remains in string fields, somehting can LOOK
null and not be so. Its all a matter of personal choice. I prefer to
not have fields empty.

How can you know none of the values are null? If a user deletes the date,
you will need code to replace the null with #1/1/1901#.

[DavidB]

On Aug 21, 11:48 am, "paii, Ron" <[e-mail]:

"DavidB" <[e-mail] in message

news:11**********************@57g2000hsv.googlegro ups.com...

On Aug 20, 11:39 pm, "Larry Linson" <[e-mail]:

"DavidB" <[e-mail]

IMHO especially in my example of dates, there should never be a null
date. If you are doing any sort of date comparisions, calulations,
etc, allowing nulls only muddies the waters. With strings, it is not
that difficult to have the string " " entered which LOOKS LIKE it is
null but in all actuality isn't.

No matter what you use to represent the fact, a date that has not been
entered is not a valid date, and should not be used in comparisons,
calculations, etc.. Using null does not "muddy the water", it

proliferates

through the calculations as "null", which, if you like, you can format

as

"NA" when you present it to the user.

Larry Linson
Microsoft Access MVP

Whether the 'nothing' data is an actual date or null is irrelevant in
your case. You can represent any set value as NA in output be it null
or #1/1/1901#. However if you are wriing a query on a date field, if
you know none of the values are null, thats one less degree of
complexity that you have to cdoe for. So indeed having null does
muddy the code. The fact remains in string fields, somehting can LOOK
null and not be so. Its all a matter of personal choice. I prefer to
not have fields empty.

How can you know none of the values are null? If a user deletes the date,
you will need code to replace the null with #1/1/1901#.- Hide quoted text -

- Show quoted text -

You do realize you can make a field required right? You can do this
in the table properties or you can ensure no nulsl via code. Bottom
line it is very easy to ensure that there are no nulls in a given
field of any of your tables.

[paii, Ron]

"DavidB" <je***@yahoo.comwrote in message
news:11**********************@w3g2000hsg.googlegro ups.com...

On Aug 21, 11:48 am, "paii, Ron" <[e-mail]:

"DavidB" <[e-mail] in message

news:11**********************@57g2000hsv.googlegro ups.com...

On Aug 20, 11:39 pm, "Larry Linson" <[e-mail]:
"DavidB" <[e-mail]

IMHO especially in my example of dates, there should never be a

null

date. If you are doing any sort of date comparisions,

calulations,

etc, allowing nulls only muddies the waters. With strings, it is

not

that difficult to have the string " " entered which LOOKS LIKE it

is

null but in all actuality isn't.

No matter what you use to represent the fact, a date that has not

been

entered is not a valid date, and should not be used in comparisons,
calculations, etc.. Using null does not "muddy the water", it

proliferates

through the calculations as "null", which, if you like, you can

format

as

"NA" when you present it to the user.

Larry Linson
Microsoft Access MVP

Whether the 'nothing' data is an actual date or null is irrelevant in
your case. You can represent any set value as NA in output be it null
or #1/1/1901#. However if you are wriing a query on a date field, if
you know none of the values are null, thats one less degree of
complexity that you have to cdoe for. So indeed having null does
muddy the code. The fact remains in string fields, somehting can LOOK
null and not be so. Its all a matter of personal choice. I prefer to
not have fields empty.

How can you know none of the values are null? If a user deletes the

date,

you will need code to replace the null with #1/1/1901#.- Hide quoted

text -

- Show quoted text -

You do realize you can make a field required right? You can do this
in the table properties or you can ensure no nulsl via code. Bottom
line it is very easy to ensure that there are no nulls in a given
field of any of your tables.

Yes you can make the field required but what value will your users enter?
The default is used only on new records. I would rather deal with null then
random dates.

[David W. Fenton]

DavidB <je***@yahoo.comwrote in
news:11**********************@57g2000hsv.googlegro ups.com:

2) Your NULL is default only for new records as well. Thats 100%
irrelevant.

No, it's also the value if the user DELETES the entered value.

Which was Ron's point.

Which you seem to have missed.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"paii, Ron" <no**@no.comwrote in
news:Nt******************************@athenet.net:

In most cases I find it equally
easy to detect NULL as default values. The extra work will protect
my applications from those NULL values.

Null always means the same thing.

A "magic date" will not be clear to another developer (or another
application).

By choosing a "magic date," you're making the enforcement of your
data schema and the meaning of it dependent on application-level
logic. That's bad.

Null as default has none of those problems at all.

When I was inexperienced, I found Nulls frustrating, but once I
understood them, I find them vastly preferable, especially for the
ability to propagate them in concatenation operations.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/