If and else date expressions

[Max]

Having problems with a date expression in a report. I
want to calculate the days between two fields - "Date
Sent to" and "Date Returned".

If "Date Returned" is null use current date( Now() )
minus "Date Sent to" to get the amount of days. Else
use "Date Returned" - "Date Sent to"

Can anyone assist????

Thanks

 

[Allen Browne]

Try setting the Control Source of your text box to:
=DateDiff("d", [Date Sent to], Nz([Date Returned], Date()))

 

[fredg]

Having problems with a date expression in a report. I
want to calculate the days between two fields - "Date
Sent to" and "Date Returned".

If "Date Returned" is null use current date( Now() )
minus "Date Sent to" to get the amount of days. Else
use "Date Returned" - "Date Sent to"

Can anyone assist????

Thanks

Here are 2 methods:

=IIf(IsNull([DateReturned]),DateDiff("d",Date(),[DateSent]),DateDiff("d",[DateReturned],[DateSent]))

or..

=IIf(IsNull([DateReturned]),Date()-[DateSent],[DateReturned]-[DateSent])