Field on Form with Expression that shows number of days overdue but excludes negative

[frenzied_female]

Hi Everyone,

I have a text box on my form that needs to show the number of days an item is overdue using the Due Date, minus the open date. I need to exclude negative days and only show genuinely overdue items.

I have this: =IIf(IsNull([Due Date of Current Action]),DateDiff("ww",[Opened Date],Now()))>1

But it shows the overdue days plus anything that is not yet due as a negative number.

Many thanks for any help.

Regards,
Kim

 

[arnelgp]

just use Format of the textbox (Design View,
Property Sheet->Format->Format)

0;"";0

this will hide the negative values.

 

[arnelgp]

... or

you can use conditional formatting
on the field and set its forecolor
to white

... or

you can use this formula on the
textbox:

=IIf(IsNull([Due Date of Current Action]),IIf(DateDiff("d",[Opened Date],Date())<0,Null,DateDiff("d",[Opened Date],Date())),Null)

 

[moke123]

0;"";0

nice trick Arnel. Dont think i've seen that before.
testing it out I find 0;"" also works.
Not sure why it works but i'll have to tuck it away somewhere for the future.

 

[frenzied_female]

Thank you so much, Arnel! The 0;"";0 did the trick