Null Values in Queries

  • Thread starter virtualkeeper via AccessMonster.com
  • Start date
V

virtualkeeper via AccessMonster.com

I have a query that I'm trying to insert a zero for a value if there is none
present in the field but it is giving me the wrong answer. The premise is
that you start with a paint mixture (Application1) and subtract the leftover
(OzLeft) and mulitply that by .117825 to get the gallons and then multiply
that by the Volitile Organic Compounds then divide the result by the amount
of spraying time (time end - time start) to arrive at a VOC per hour output.

LbsHr2: Sum(((Nz([Application1],0)-Nz([OzLeft],0))*0.007825)*[VOC])/(Nz(
[TimeEnd],2)-Nz([TimeStart],1))

Why is this mathmatically giving me a wrong answer when there ARE values in
the fields? I've racked my brain on this one and can't figure it out.
 
D

Daryl S

Try the following:

LbsHr2: Sum(((Nz([Application1],0)-Nz([OzLeft],0))*0.007825)*[VOC])/((Nz(
[TimeEnd],2)-Nz([TimeStart],1)))

The extra parentheses will cause the time to be subtracted before being
divided into the other values. Otherwise, you are only dividing by the
TimeEnd, then subtracting the TimeStart.
 
M

Marshall Barton

virtualkeeper said:
I have a query that I'm trying to insert a zero for a value if there is none
present in the field but it is giving me the wrong answer. The premise is
that you start with a paint mixture (Application1) and subtract the leftover
(OzLeft) and mulitply that by .117825 to get the gallons and then multiply
that by the Volitile Organic Compounds then divide the result by the amount
of spraying time (time end - time start) to arrive at a VOC per hour output.

LbsHr2: Sum(((Nz([Application1],0)-Nz([OzLeft],0))*0.007825)*[VOC])/(Nz(
[TimeEnd],2)-Nz([TimeStart],1))

Why is this mathmatically giving me a wrong answer when there ARE values in
the fields? I've racked my brain on this one and can't figure it out.
Click to expand...


Because of the way date/time values are stored,
Nz([TimeEnd],2)-Nz([TimeStart],1) will not give you a result
in hours. It results in days and fraction of a day. You
might be able to get what you want if you multiply by 24
. . . / (Nz(24 * [TimeEnd], 2) - Nz(24 * [TimeStart], 1))
but you could still end up with a mess if TimeStart or
TimeEnd, but not both, are null.

Technically, you should not rely on knowledge of how
date/time values are stored. Instead you should use the
DateDiff function like:
. . . / Nz(DateDiff("n", TimeStart, TimeEnd) / 60, 1)
Note this will return 1 hour if either or both time values
are null.
 
Top