302 Found

Found

The document has moved here.

Knowledge Base

Our Knowledge Base covers a wide variety of technical topics, from the basic to the complex.

I need to calculate the change in value of the account using the latest quarter and subtracting the initial value. How do I do this?

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.

Comments

Post new comment

The content of this field is kept private and will not be shown publicly.
Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.
302 Found

Found

The document has moved here.