Calculation using two dates

[BaaBaaBristol]

Hello!
I wondered if anyone could help me with a slightly more complex date calculation. There are two groups of clients that affect a calculation showing the length of engagement with our project:



ACTIVE CLIENTS: Difference between Start Date and today's date.
CLOSED CLIENTS: Difference between Start Date and End Date

Start Date and End Date are entered on the main data entry form, but active clients will only have the start date.

I have found the following formula before, which I assume would work with active clients:

=DateDiff ("d",Date (), [StartDate])

My questions is how do I adapt this formula to cover both instances of active and closed clients, to see if there is a value in Date Closed - if this is null, due to not ending yet, then I need it to continue comparing to today's date.
I hope that is clear - I can clarify further if not.

[Welshgasman]

Use IIF() function to determine which calculation and NZ() function to use Date() for active clients

[BaaBaaBristol]

Thanks for your quick reply. Would you be able to illustrate an example of IIF that gives the calculation expresses in the case of TRUE and FALSE?

[Micron]

IIF(IsNull([End Date]),Date-[Start Date],[End Date]-[Start Date])

Could depend on where you're doing this. Maybe your post really belongs in Queries forum? The forum choice often provides useful clues to the responders.

[BaaBaaBristol]

Thanks for the reply. I am not sure where to put this formula. I have added a field called 'JourneyLength' to the main table that is acting as a client record. This table also holds fields 'DateFrom' and 'DateTo' for start and end dates.

On the input form, I am using a text box which I am linking to 'JourneyLength' as the Control Source. I have tried putting the formula in as code for an event, but the syntax is rejected. I am only using:

IIF(IsNull([DateTo]),Date-[DateFrom],[DateTo]-[DateFrom])

Am I missing another function to make this work, or have I got the syntax or placement wrong?

[Welshgasman]

Me.JourneyLength = IIF(IsNull([DateTo]),Date-[DateFrom],[DateTo]-[DateFrom])