Hello both,
Thanks for answering so quickly! However, none of the suggestions was
successful in solving the problem.
Maybe it's better to describe the problem in more detail. The database is
aimed at recording data related with experiments. The idea with this query is
to retrieve data from tables I've already set up so that I could produce
labels for the samples being analysed.
In these experiments samples need to be incubated with the reagents for a
number of days and will be tested daily until a specific characteristic is
observed. At this point the sample is no longer needed and so I wouldn't need
to produce labels from that day onwards.
Say I add the reagent on day 0 to a collection of samples and observe this
characteristic in one of the samples on day 5. Even though other samples will
be further tested, this wouldn't.
The query is based on two tables:
In the table "Sample Results" I record the number of the sample and when the
specific characteristic was observed (fields: "sample number" and "day
observed", both number formats, but "day observed" only gets completed when a
result is observed).
The second table "Reagents Addition" will contain more details about the
specific reagents added each day to all the samples being tested at each
specific day (relevant fields: "Test day", number, no Null values and "sample
number", the field that will link to the first table using an intermediate
table with experiment details).
In the query I retrieve data from both tables to create the labels and I
wanted to eliminate any records for which the "test day" is bigger than the
"day observed" field, maintaining any fields for which this hasn't been added.
At the moment the expressions don't seem to be working properly, not only in
the fields that have no values but also returning "-1" when I have a "day
observed" of 1 and a "test day" 2. Could this be related to the fact that
"day observed" is a lookup column for the "test day" (a combo box with test
day, date)?
I hope this made it a bit clearer...
Many thanks,
Ana
Ken Sheridan said:
Ana:
The "-" is because Boolean TRUE and FALSE values are implemented as -1 and 0
in Access. Its better programming practice to use the Constants TRUE or
FALSE, however.
You don't say whether NumberOfItems can also be Null, so to be on absolutely
safe ground:
Expr1:Nz([NumberOfItems],0) <= Nz([Stock],0)
It would probably be advisable to disallow Nulls in the Stock column. You
can first insert zeros in place of any Nulls with an update query:
UPDATE YourTable
SET Stock = 0
WHERE Stock IS NULL;
Then set the Stock column's Required property to True (Yes) in table design
and its DefaultValue property to 0.
Do the same for the NumberOfItems column if necessary.
Ken Sheridan
Stafford, England
:
I'm developing a database in Access and in one of the queries I am trying to
set up I want to compare two numeric fields and get a true or false result.
However, one of the fields (Stock) contains null values which is turning this
into a difficult task.
I have created an expression equivalent to this:
Expr1: Nz([NumberOfItems]<=[Stock],"-1")
It doesn't seem to be working properly though as it returns some values "-1"
(why "-" by the way?) even if the first field has a higher number than the
second.
Any help would be much appreciated!!
Thanks,
Ana