Auto-populate a 'Days outstanding' field

[Jay]

I have a simple Customer Query database, which has two date fields:

Date Received
Date Resolved

I want to have another field, called 'Days outstanding' in the same table
which automatically displays the number of days the query has been
outstanding (i.e not reolved). So the days outstanding will be either:
The Date Resolved minus the Date Received (if the query has been resolved) .
Or if the query remains unresolved & the Date Resolved field is Null, then
the Days outstanding will be Now() minus the Date Received.

I'd love to be able to incorporate this but don't know where to start. Can
you SUM dates like in Excel? ANd how would I incorporate the using Now()
instead of Date Resolved if the Date Resolved field is Null?

I'd really appreciate any help.

Many thanks,

Jason

 

[Graham Mandeno]

Hi Jason

Do not store this value in a field in your table!

There are two reasons why not:
1. You should never store values that can easily be calculated from other
fields
2. Until the query is resolved, the value would be constantly changing

Instead, create a query based on your table and add a calculated field to
the query:

DaysOutstanding: DateDiff( 'd', [DateReceived], Nz( [DateResolved],
Date() ) )

Nz is a handy little function that says: "If the first argument
(DateResolved) is not null then use it, otherwise use the second argument
(the current date)"

DateDiff calculates the difference between two dates in the unit given (in
this case 'd' = days).

 

[Guest]

You should not have another field, called 'Days outstanding' in the same
table as data always changes. Just calculate it on the fly - each time you
need it.
Days outstanding: DateDiff("d", [Date Received], IIF([Date Resolved] Is
Null, Date(), [Date Resolved]))