Creating a Calculated Field Using IIF or NZ

[rjwell]

Hello,



I'm working on a query used for a report that tracks a calculated field I'm calling "Aged Holds" of which I'm looking for production holds older than 7 days. To create this calculated field I need to use 3 other fields; hold date, release date, and reject date. Hold date will always have a value and is never null but release date and reject date will be null when the other is not or both null at the same time if production holds are still active. To create my calculated field I need to subtract the hold date from either release date or reject date when they are not null values. I've used the NZ function with release date and hold date to create the calculated field with but how do I incorporate reject date?

This is what I currently have using:
Hold_Age: nz([RELEASE_DATE],Date())-[HOLD_DATE]

[Rhino373]

Something like this:

=IIF([Release_Date] is null, IIF([Reject_Date] is null,Date() - [Hold_Date],[Reject_Date] - [Hold_Date],[Release_Date] - [Hold Date])