problems with changing color depending on Date

[ian_ok]

I'm trying to highlight a field if the date is less than or even equal to today on a continuous form.

This is what I've tried all to no avail....

Private Sub Form_Load()
Me.Form.OrderBy = "1st Payment Date Limit"
Me.Form.OrderByOn = True

If [1st Payment Date Limit] <= Now() Then

' If Me.Ctl1st_Payment_Date_Limit <= (Date + 2) Then

' If (Me.Ctl1st_Payment_Date_Limit <= Date) Or (Me.Ctl1st_Payment_Date_Limit >= (Date + 2)) Then
 'If (Me.Ctl1st_Payment_Date_Limit <= Now()) Or (Me.Ctl1st_Payment_Date_Limit >= (Now() + 2)) Then


Me.Ctl1st_Payment_Date_Limit.BackColor = vbRed
Else
Me.Ctl1st_Payment_Date_Limit.BackColor = vbWhite
End If
End Sub

As you can see I've tried different methods and none seem to work, I can get the all the fields to change to red, but I want only the ones which are in the past or today to do so.

Thanks for any pointers.

Ian

 

[pbaldy]

Have you tried Conditional Formatting? Code doesn't work well for this purpose in a form.

 

[ian_ok]

Thanks Paul, works wonders, didn't know Access had that, I knew about in Excel....Doh....!!

Ian

 

[pbaldy]

Happy to help Ian. Code like that can work in a report, but not in a form. Conditional Formatting is usually easier anyway.