Subtracting Prev YTD from Current YTD where Prev is Null is always returning Null value

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

I'm trying to subtract Previous YTD values from Current YTD values.
There are instances in Current YTD where it is a new item, so no
previous YTD value exists. so as examples:

Current YTD = 10 and Previous = 7, result is 3
Current YTD = 10 and Previous = 10, result is 0

But if Current YTD = 10 and Previous = Null, the result I need is 10,
but I get Null

My formula is: Weekly Total: Sum(([Current YTD]-[Previous YTD]))

I have instances of adjustments where the result can be negative, so I
cannot use and absolute value.

Any ideas?
Thanks!
 
Try using the Nz function to replace a Null value with zero:

Weekly Total: Sum((Nz([Current YTD],0)-Nz([Previous YTD],0)))
 
Try using the Nz function to replace a Null value with zero:

Weekly Total: Sum((Nz([Current YTD],0)-Nz([Previous YTD],0)))

--

Ken Snell
<MS ACCESS MVP>




I'm trying to subtract Previous YTD values from Current YTD values.
There are instances in Current YTD where it is a new item, so no
previous YTD value exists. so as examples:
Click to expand...
Current YTD = 10 and Previous = 7, result is 3
Current YTD = 10 and Previous = 10, result is 0
Click to expand...
But if Current YTD = 10 and Previous = Null, the result I need is 10,
but I get Null
Click to expand...
My formula is: Weekly Total: Sum(([Current YTD]-[Previous YTD]))
Click to expand...
I have instances of adjustments where the result can be negative, so I
cannot use and absolute value.
Click to expand...
Any ideas?
Thanks!- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thank you....that worked perfectly!
 
Back
Top