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.
15  12092 
On Aug 20, 1:15 pm, Mr.Tom.Will...@ 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.
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
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.
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.
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.
<Mr************ @gmail.comwrote in message
news:11******** *************@a 39g2000hsc.goog legroups.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.
"DavidB" <je***@yahoo.co mwrote in message
news:11******** **************@ d55g2000hsg.goo glegroups.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
On Aug 20, 1:49 pm, "Larry Linson" <boun...@localh ost.notwrote:
"DavidB" <je...@yahoo.co mwrote in message
news:11******** **************@ d55g2000hsg.goo glegroups.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.
DavidB <je***@yahoo.co mwrote in
news:11******** **************@ 57g2000hsv.goog legroups.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/
"DavidB" <je***@yahoo.co mwrote
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Wally |
last post by:
I have a record set (rs) that contains 25 barcodes values that I set to true
in a cookie. (see code section below)
If I read the cookie from within the same page that created it, I see all
25 values. But if I redirect to another page and read the cookie only 18 of
the values remain.
Even though I load the same 25 barcodes at login, the 18 that remain can
differ each time.
|
by: KC |
last post by:
Hello,
I am using Access 2002. WinXP, Template from MS called Orders Mgmt DB.
I have tweaked this DB to work for our small co.
It has worked pretty well up until I made the mistake of deleting about
80 records from the Orders table. 80 out of a 1000 records.
Now our data entry form shows our customer addresses, but not customer
order history. When looking at all of the tables, customer, payments,
orders, they still have all of the...
|
by: Chris |
last post by:
I finally got my report to be recongized, that login issue is terrible. Using
VS.NET 2003, created report inside of it. Now when I attempt to display the
report is blank (missing data, though I can see the viewer, report fields,
etc.) I've tried setting the
rpt.SetDataSource(dstConcernInfo) in the reports VB code behind and my
viewer code-behind page. No luck. I've displayed my dataset to see that it's
full. I assume that the report can't...
|
by: cameron |
last post by:
I am working on an assignment where I have to allow end-users to enter in data and to have C++ make a calculation. From there I have to set up a loop to allow users to go back and enter new data or just quit. I have the part down where the users are entering in information to get a calculation, but I am not understanding the part about looping to go back, any direction will be greatly appreciated. Here is my code for now:...
|
by: danceli |
last post by:
After loading the BCP files that are created during the trigger/
reporting events I've noticed that the data in the table is missing
records. I've also noticed that the missing records (records in the
table but not in the BCP out files) seem to occur in contiguous
blocks. Since the complete set of records exists in the table, I
assume this points to an issue in the way the TableUpdate script/
Triggers interact with the system. But i tried...
| | |
by: danceli |
last post by:
I have made trigger on table 'FER' that would be fired if data is
inserted, updated to the table. And also, I made batch file using bcp
to extract the newly updated / inserted records.
But I got missing data in bcp out file like this:
Missing 1200 records, blocked at:
/*
777946 296188 2007-01-29 21:25:45.063
|
by: anubis2k7 |
last post by:
Hi,
I am having a problem with dynamically sorting/grouping data in my report at runtime. My problem is that when the report is run using sorting/grouping I am missing data.
Specifically, my situation is that I have a query that contains results from 2 tables that have a one to many relationship to each other (ex. 1=Contact, M=Contact Incident, 1 Contact can have multiple Contact Incidents). This query is the dataset for my report.
I...
|
by: soccery387 |
last post by:
Hi,
I am very much a beginner to Access, but learn software very quickly. I made a simple project with a table, a form to enter the data and a report to view/print the data. I added all the data in manually on the table and in my form. When I go to the report print view, some of the textfields have the values in them like in the table, but others are missing the value. I don't see how this is possible seeing as some values are there and...
|
by: sirvi |
last post by:
what validation rule can be used to show :we cannot enter a data in a field, when another field does not have data.for example we can not enter details of a person untill we dont enter his name.
|
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:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
