Date calculation with certain criteria

[mattP]

All,

I have searched the threads but cannot find anything specifically to what I need.
I track escalations with my new DB and extract monthly reports into Excel to produce charts.
I have a field to log the age of the escalation but I need the following criteria.

If the Status of the escalation is "Open", then calculate the age between "Date Open" and "Date",
If the status is "closed" then use the "DateDiff" command between date opened and "Date Closed".

I've got this working in a seperate form but I need it to work in a query without any forms being opened and I am not sure on how to go about this.

Many thanks

MattP

 

[Brianwarnock]

In a calculated field in the query enter
fldAge:iif([status]="open",Datediff("d",[Date Open].date()).Datediff("d",[Date Open],[Date Closed]))

Or something like that

Brian