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

[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!

 

[Ken Snell \(MVP\)]

Try using the Nz function to replace a Null value with zero:

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

 

[Ben]

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:

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!- Hide quoted text -

- Show quoted text -

Thank you....that worked perfectly!