Date query with null values 1

[Smack]

In a query i want to show how many days an action has been open. I have 2 date fields, opened closed.
Using Expr1: ([Closed])-([Opened])is easy for records which have been closed, but how to calculate opened to current dat if the closed field is null?
-Smack >::O>

 

[tlbroadbent]

Use the IIf and datediff functions.

Expr1: datediff("d", IIf([Closed] Is Null, date(), [Closed]), [Opened])

The IIf functions substitutes today's date for [Closed] if [Closed] is null. You can use that expression in your datediff calculation. This will calculate the number of days between the dates. Terry

http://members.home.net/tlbroadbent/index.htm

 

[Smack]

Excellent!Copy Paste and BAM!!
Thanks alot.
-Smack

 

[Smack]

As usual, now that I have added that to the report, "they" want something else!
In using the above expression, how would I tie in a third date field, Date Due, to calculate not only the days open, but also days past due for when the date closed surpasses date due? LOL

 

[tlbroadbent]

You need to create two fields.

OpenDays: datediff("d", IIf([Closed] Is Null, date(), [Closed]), [Opened])

DaysPastDue: datediff("d", IIf([Closed] Is Null, date(), [Closed]), [DateDue]) Terry

http://members.home.net/tlbroadbent/index.htm

 

[Smack]

Once again - BAM! (Don't know why I'm on this Emeril thing)
One other question, The expressions give days open and overdue days as negative numbers. How to transpose?
-Smack

 

[tlbroadbent]

Like this...

OpenDays: datediff("d", [Opened], IIf([Closed] Is Null, date(), [Closed]))

DaysPastDue: datediff("d", [DateDue], IIf([Closed] Is Null, date(), [Closed])) Terry

http://members.home.net/tlbroadbent/index.htm

 

[Smack]

Thank you Terry, that worked great.
Smack