I am trying to compare some dates in a form, to the current date.
Here are some fields in my form:

DocOwnerRevDate - Document Owner Review Date
DocOwnerDate - Document Owner Date

OCARRevDate - Operations Committee Review Date
OCARDate - Operations Committee Date

OpenClosed - Signifies Whether Project is Open or Closed


Dates will be filled into the date fields. If the DocOwnerRevDate
field is less than the current date, then I would like the text in
that field (the label and field itself) to turn RED. That is unless
the DocOwnerDate field is filled in. If the DocOwnerDate field is
filled in, then text should appear as normal (in black font, etc.). I
would like the same done with the OCARRevDate and the OCARDate fields.
I have tried putting some code in the OnCurrent event of the form,
and was able to get the text and label to turn red, but that is just
based on whether or not the DocOwnerDate is NULL. I am unable to put
any other stipulation in there, to compare the DocOwnerRevDate to the
current date. Finally, all of these criteria are dependant upon
whether or not the job is Open. If the job is Closed, then none of
this should take place.

Example:

DocOwnerRevDate: 6/17/2004 (current date happens to be 7/26/2004)
DocOwnerDate has not been filled in.
OUTCOME ON FORM SHOULD BE THAT THE DocOwnerRevDate field turns RED.

Any help on this issue would be greatly appreciated!

Thanks in advance,


Jonathan

Although I am not an expert in Access, the following code should do
the required job.

Create a subroutine in the form for updating the color of dates. Here
I have assumed the field "OpenClosed" to be true for open projects and
false for closed ones.

Sub updateDateField()
If Me.OpenClosed And IsNull(Me.DocOwnerDate) And
Nz(Me.DocOwnerRevDate) < Date Then
Me.DocOwnerRevDate.ForeColor = RGB(255, 0, 0)
Me.DocOwnerRevDate_Label.ForeColor = RGB(255, 0, 0)
Else
Me.DocOwnerRevDate.ForeColor = RGB(0, 0, 0)
Me.DocOwnerRevDate_Label.ForeColor = RGB(0, 0, 0)
End If
End Sub

2. On each of the following events, call the updateDateField
subroutine:

Private Sub Form_Current()
Call updateDateField
End Sub

Private Sub OpenClosed_Click() 'I have assumed it to be a check box
Call updateDateField
End Sub

Private Sub DocOwnerDate_AfterUpdate()
Call updateDateField
End Sub

Private Sub DocOwnerRevDate_AfterUpdate()
Call updateDateField
End Sub

Hope this helps.