Return Zero not Null

  • Thread starter chopper57 via AccessMonster.com
  • Start date
C

chopper57 via AccessMonster.com

How would I place the Nz function is the following:

Sum(IIf([Finish]>[Start],([Finish]-[Start])*24,24-([Start]-[Finish])*24)-
[Breaks])

the fomula works, but I need to show reports that have no hours also.
I've been inserting the Nz, with no results so far.
 
W

Wayne Morgan

If you put Nz() around each field name, then any field that is Null would be
changed to zero for the calculation. So, if you had a value in Start but not
one in Finish, you would get (Start-0*24).

Example:
Sum(IIf(Nz([Finish],0)>Nz([Start],0),(Nz([Finish],0)-Nz([Start],0))*24,24-(Nz([Start],0)-Nz([Finish],0))*24)-Nz([Breaks],0))


If all you want is to get a zero out if any of the fields is Null, then you
could get by with placing Nz() around the IIf().

Example:
Sum(Nz(IIf([Finish]>[Start],([Finish]-[Start])*24,24-([Start]-[Finish])*24)-[Breaks],
0))
 
C

chopper57 via AccessMonster.com

Thanks Wayne, that got it!

Wayne said:
If you put Nz() around each field name, then any field that is Null would be
changed to zero for the calculation. So, if you had a value in Start but not
one in Finish, you would get (Start-0*24).

Example:
Sum(IIf(Nz([Finish],0)>Nz([Start],0),(Nz([Finish],0)-Nz([Start],0))*24,24-(Nz([Start],0)-Nz([Finish],0))*24)-Nz([Breaks],0))

If all you want is to get a zero out if any of the fields is Null, then you
could get by with placing Nz() around the IIf().

Example:
Sum(Nz(IIf([Finish]>[Start],([Finish]-[Start])*24,24-([Start]-[Finish])*24)-[Breaks],
0))
How would I place the Nz function is the following:
Click to expand...
[quoted text clipped - 3 lines]
the fomula works, but I need to show reports that have no hours also.
I've been inserting the Nz, with no results so far.
Click to expand...
Click to expand...
 
Top