DateDiff function and conditional formatting 1

[ychousa]

Hi. I'm trying the following function to change the background color of a textbox if the date difference between today and expire date is less than 120 days.

The form is a continuous form and a textbox to show expiration date is called Expires.

Private Sub Form_Current()

Dim vDiff As Long

vDiff = DateDiff("d", Now, Expires)

If (vDiff < 120) Then
Me.Expires.BackColor = vbYellow

Else

End If

End Sub

However this function changes all the Expires textboxes' background to yellow in my continuous form.

Can anyone give me a suggestion?

Thanks in advance!

 

[jimb0ne]

switch Now and Expires in the datediff statement.

 

[jimb0ne]

so basically vDiff = DateDiff(&quot;d&quot;, Expires, Now) out of curiosity, why aren't you using
vDiff = DateDiff(&quot;d&quot;, Date, Expires)

 

[ychousa]

Thanks for your reply, but I already tried your suggestion. Your suggestion actually gives all negative values when I Debug.Print, so I think the one I posted is right, calculation-wise.

I think my condition is not working, but I can't think of a reason.

 

[ychousa]

I tried Date, but it didn't work, so I'm trying with Now-both give the same result.

 

[RoyVidar]

Hi!

I'm not sure if the programming approach works without &quot;serious tweaking&quot;. There's been some threads about it, you might try a keyword search.

But:
If your version supports conditional formatting (I think a2000+ versions), select the &quot;Expires&quot; control. Use Format | Conditional formatting, select &quot;Expression is&quot; in the first combo. then enter your expression in the next field.

[tt]DateDiff(&quot;d&quot;, Date(), [Expires])<120[/tt]

HTH Roy-Vidar

 

[ychousa]

Hi, Roy-Vidar.

Your suggestion worked!

Thank you.