Conditional format by date

[Webster01]

I would like the background of a text box (date) to change based on the "age" of the date. For example, If the text box date is greater than 60 days ago the background would be yellow and if the text box date is greater than 120 days ago, the background of this text box would be red. I continually get "syntax error" when I try to enter an expression for the conditional format and I am not sure how to distinguish 30 days from 30 years. Thanks.

 

[Graham T]

Webster

The following may provide a work around for you, although not using conditional formatting......

On my form I have an OrderIDNumber, InvoiceDueDate and DaysOverdue field.

The DaysOverdue uses the DateDiff function to work out how many days have elapsed between InvoiceDueDate and Todays Date: =DateDiff("d",[InvoiceDueDate],Date())

Then in the On Current Event of the form and the After Update event of the InvoiceDueDate I have used the following Case Statement:

'Check to see if InvoiceDueDate is Null

If IsNull(Me.InvoiceDueDate) Or Me.InvoiceDueDate = "" Then
Me.InvoiceDueDate.BackColor = vbWhite
End If

'Check DaysOverdue field to calculate case

Select Case DaysOverdue
Case 0 To 30
Me.InvoiceDueDate.BackColor = vbWhite
Case 31 To 60
Me.InvoiceDueDate.BackColor = vbYellow
Case Is > 61
Me.InvoiceDueDate.BackColor = vbRed
End Select

Hopefully this should point you in the right direction, please see attached Access 2K example form.

Graham