Using IIf and DateDiff #Error

[Opal]

I am trying to create a field in my report which will show the number
of days between the LossDate and Today's Date if the issue has a
status of "Open" and if the status is not open (i.e. Closed) I want
to see the number of days between the LossDate and the CMDate
(closed date). I have tried:

DateDifference: IIf ([Status]="Open",DateDiff("d",[LossDate],
[TodaysDate],DateDiff("d",[LossDate],[CMDate])))

but I am getting #Error in the field. Any suggestions?

 

[Michel Walsh]

You first DateDiff should, probably, get a ) after [TodaysDate].

Having add it, remove one of the three ) at the end.



DateDifference: IIf (Status="Open",DateDiff("d",LossDate,
TodaysDate),DateDiff("d",LossDate,CMDate))




Vanderghast, Access MVP

 

[Evi]

You've got one of those pesky brackets in the wrong place,Opal. Try.
DateDifference: IIf ([Status]="Open",DateDiff("d",[LossDate],
[TodaysDate]),DateDiff("d",[LossDate],[CMDate]))

Evi

 

[Opal]

You first DateDiff  should, probably, get a ) after [TodaysDate].

Having add it, remove one of the three ) at the end.

 DateDifference: IIf (Status="Open",DateDiff("d",LossDate,
 TodaysDate),DateDiff("d",LossDate,CMDate))

Vanderghast, Access MVP

I am trying to create a field in my report which will show the number
of days between the LossDate and Today's Date if the issue has a
status of "Open" and if the status is not open (i.e. Closed) I want
to see the number of days between the LossDate and the CMDate
(closed date).  I have tried:

DateDifference: IIf ([Status]="Open",DateDiff("d",[LossDate],
[TodaysDate],DateDiff("d",[LossDate],[CMDate])))

but I am getting #Error in the field.  Any suggestions?- Hide quoted text -

- Show quoted text -

Thank you, both Michel & Evi, that got rid of my #Error

Thank you Thank you!