Trouble with a countdown field expression which works off 2 potential fields using IIF

[tom89]

Hello,



Hope this is in the right forum!

I'm trying to complete an unbound countdown TextBox called 'DaysToComplete. However there is not always going to be a value in one of the fields (ImplementationDate) when a value is present in the ImplementationDate field I need it to use this date, if not then I need the countdown to use the field (DueDate) The DueDate field will always be populated. The 'DaysToComplete' entry is always working from the present day.

This was the expression we use and know works in Excel.

=If([ImplementationDate]="",Int([DueDate]-Date()),Int([ImplementationDate]-Date()))


We replaced the if with iif and put into access but no luck.


This is the latest one I have tried and the records return as '#Name?' so a little bit stuck now.

=IIf(IsNothing([Fields]![ImplementationDate].[Value]),DateDiff("d",Date(),[Fields]![DueDate].[Value]),DateDiff("d",Date(),[Fields]![ImplementationDate].[Value]))


If anyone has any ideas where we're going wrong or even a new approach to give us the desired result that would be of huge help!

[ranman256]

not so in Access:

=iIf(IsNull([ImplementationDate]),dateDiff("d",[DueDate],Date()),DateDiff("d",[ImplementationDate],Date()))

[pbaldy]

Or maybe

=DateDiff("d",Nz([ImplementationDate], [DueDate]), Date())

[tom89]

Thanks a lot, that works perfectly.