Expression comparing two values yields wrong results

[Ana]

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

 

[Dale Fye]

Ana,

You use the NZ( ) function to return an alternate value for a field that is
null. So, you might try:

Mismatch: IIF(NZ([NumberOfItems], -1) <> [Stock], True, False)

This would give you a True value if [NumberOfItems] is NULL or is not equal
to the value in the [Stock] field (assumes that [Stock] cannot be a -1).

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Ken Sheridan]

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

 

[Ana]

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

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

 

[Ken Sheridan]

Ana:

It may well be. 'Lookup columns' are evil. For the reasons why see:


http://www.mvps.org/access/lookupfields.htm


In a query you should not rely on the displayed data in a 'lookup column'
but always join the referenced table to the referencing table and return the
true value from the referenced table.

It seems to me that you would be far better off storing true date/time
values rather than day numbers as the latter can be computed from the former.
If a reagent is added on date d and tested on date t then test day number td
can be computed simply with:

td = t – d

This denotes date t as td 0, but if you want it denoted as 1 then:

td = (t – d) + 1

If the sample is observed on date od then t is 'bigger' (later) than od
where t > od.

You want to eliminate those rows from the result set however, so the
criterion for this in the query would be where t <= od. This would return
those rows where od is not Null and t is on or before od. It will not return
those rows where od is Null because any comparison with a Null produces a
Null, not a True or False (this is even so if you compare Null with Null).
You can ensure that the rows with Null od are also returned however by
testing for od IS NULL. The operation is a Boolean OR, so the query's WHERE
clause would contain the following expression:

WHERE (t <= od OR od IS NULL)

You would of course give the columns more meaningful names than the d, t and
od I've used.

By using true date/time values and computing the test day number as
described above you eliminate the need for 'lookup columns'. You can still
use the combo box to select a date for the day observed column when entering
data, but its bound column would be the date not the test day.

Keeping the test day column rather than computing it would not change the
modus operandi outlined above, but be aware that, because this value can be
computed, it does introduce redundancy and the possibility of inconsistent
data being stored. Computing values which can be derived from other values
on the other hand ensures consistency.

Ken Sheridan
Stafford, England

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

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

 

[Ana]

Thanks!! Your reply was very helpful! I've managed to solve the problem now!!

Ana:

It may well be. 'Lookup columns' are evil. For the reasons why see:


http://www.mvps.org/access/lookupfields.htm


In a query you should not rely on the displayed data in a 'lookup column'
but always join the referenced table to the referencing table and return the
true value from the referenced table.

It seems to me that you would be far better off storing true date/time
values rather than day numbers as the latter can be computed from the former.
If a reagent is added on date d and tested on date t then test day number td
can be computed simply with:

td = t – d

This denotes date t as td 0, but if you want it denoted as 1 then:

td = (t – d) + 1

If the sample is observed on date od then t is 'bigger' (later) than od
where t > od.

You want to eliminate those rows from the result set however, so the
criterion for this in the query would be where t <= od. This would return
those rows where od is not Null and t is on or before od. It will not return
those rows where od is Null because any comparison with a Null produces a
Null, not a True or False (this is even so if you compare Null with Null).
You can ensure that the rows with Null od are also returned however by
testing for od IS NULL. The operation is a Boolean OR, so the query's WHERE
clause would contain the following expression:

WHERE (t <= od OR od IS NULL)

You would of course give the columns more meaningful names than the d, t and
od I've used.

By using true date/time values and computing the test day number as
described above you eliminate the need for 'lookup columns'. You can still
use the combo box to select a date for the day observed column when entering
data, but its bound column would be the date not the test day.

Keeping the test day column rather than computing it would not change the
modus operandi outlined above, but be aware that, because this value can be
computed, it does introduce redundancy and the possibility of inconsistent
data being stored. Computing values which can be derived from other values
on the other hand ensures consistency.

Ken Sheridan
Stafford, England

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

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