Reply to comment
I have values for four fields representing a quarterly balance in an account. They are filled in successively over time, so at any given time they may not all be filled in.
---
This is a good place to use the Nz() function. It allows you to use a value if it is not null, but if it is null use an alternate value instead. You can cascade them to successively check each field.
Nz([Value 4], Nz([Value 3], Nz([Value 2], [Value 1]))) - [Initial Value]
The last value doesn't have an Nz around it because there is no alternate for the last one; if they all are null, then the result of the calculation will be null.
You could do this same calculation with a succession of If() and IsNull() combinations, but then you'd have to reference the value fields multiple times, making the formula more complicated to read and slower to execute.
- 35180 reads
