Change color if date is greater than another.

J

JimC

I have a form with 2 date fields. One is ExpectedDate and the other is
ActualDate. How can I make the ExpectedDate change font color if it is past
today's date and ActualDate has not been filled in?

For example, ExpectedDate is 1/1/05 and ActualDate has not been filled in
yet. I want ExpectedDate to change color to indicate it is past due and the
ActualDate has not been filled in yet?

Does this make sense?

Thanks
 
L

Luke Dalessandro

In the form's "OnCurrent" event, check the dates and set the ForeColor
based on the outcome...

Private Sub Form_OnCurrent()
If IsNull(Me![ActualDate]) And DateValue(Now()) > Me![ExpectedDate] Then
txtExpectedDate.ForeColor = 255
End If
End Sub

Then, if you want to have the color return to normal once the actual
date is filled in, change the color on the actual date after update

Private Sub txtActualDate_AfterUpdate()
If Not IsNull(txtActualDate) Then
txtExpectedDate.ForeColor = 0 ' or whatever color
Else
If DateValue(Now()) > Me![ExpectedDate] Then
txtExpectedDate.ForeColor = 255
End If
End If
End Sub

Good luck,
Luke
 
F

fredg

I have a form with 2 date fields. One is ExpectedDate and the other is
ActualDate. How can I make the ExpectedDate change font color if it is past
today's date and ActualDate has not been filled in?

For example, ExpectedDate is 1/1/05 and ActualDate has not been filled in
yet. I want ExpectedDate to change color to indicate it is past due and the
ActualDate has not been filled in yet?

Does this make sense?

Thanks
Click to expand...

Depends upon your version of Access.
Access 2000 or newer you can use Conditional Formatting.
Select the ExpectedDate control.
Click on Format + Conditional Formatting
Set the Condition1 dropdown to
Expression Is
in the dialog box alongside write:
IsNull([ActualDate]) and [ExpectedDate]>Date()

Select the color and exit the dialog.
 
Top