date diff using criteria

[CAMI Lady]

I may be going about this all wrong, if so advice always appreciated.
In the form and in the report I need to display the number of days elapsed -
if the DateResolved is blank then the number of days between SubmittedDate
and today and if the DateResolved is not blank then the number of days
between SubmittedDate and DateResolved
I have been putting it in an unbound text box on the form and report. This
is the formula I have been playing with, it only works halfway, it shows the
number of days between Submission and Resolved, but not Submission and todays
date
=IIf([DateResolved]="",DateDiff("d",[SubmittedDate],Date()),DateDiff("d",[SubmittedDate],[DateResolved]))
thanks in advance

 

[S.Clark]

Try
=IIf(Len([DateResolved])=0,DateDiff("d",[SubmittedDate],Date()),DateDiff("d",[SubmittedDate],[DateResolved]))

 

[John Spencer]

=IIf([DateResolved] is null
,DateDiff("d",[SubmittedDate],Date())
,DateDiff("d",[SubmittedDate],[DateResolved]))

OR you could use the simpler expression

=DateDiff("d",[SubmittedDate],Nz([DateResolved],Date()))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Try this --
=IIf([DateResolved] Is Null, DateDiff("d",[SubmittedDate], Date()),
DateDiff("d",[SubmittedDate], [DateResolved]))