Conditional Formatting Code

[hi there]

hi all,

i've created a public routine to perform some conditional formatting.

the objective of the code is to highlight and bold date values if the task has been completed late or is missing a value for the completed date and the due date has passed.

when testing the code in the VBE it appears to work correctly, however when the code is called via the OnFormat event on the report it is not working correctly.

there are 2 parts to the code.
1. determine if there is a value for the completed date control and if there is no completed date determine if there should have been one based upon the due date and today's date.

2. determine which tasks were completed late.

the code appears to work correctly for executing Item #1 (no completed date), but does NOT work for Item #2.



here's the code
*****************************************************
Public Sub HighlightLate(sDue As Variant, Optional sComp As Variant)

If (IsNull(sComp)) Then
If ((DateDiff("d", Date, sDue)) < 0) Then 'if the due date has passed and no info for completed date
sDue.ForeColor = 255
sDue.FontBold = True
Else 'do nothing if the due date has not approached
sDue.ForeColor = 0
sDue.FontBold = False
End If

Else
If ((DateDiff("d", sDue, sComp)) > 0) Then 'if the completed date is after the due date
sComp.ForeColor = 255
sComp.FontBold = True
Else
sDue.ForeColor = 0
sDue.FontBold = False
End If

End If


End Sub

*****************************************************

can someone tell me what i'm doing wrong?

many thanks for all help.

 

[Malcy]

I have a hunch that the OnFormat event may not be the right one to use. What happens if you move the call to OnLoad?
Might be worth experimenting a bit
HTH

 

[KeithG]

Move the function to the On_Current event of your form.

 

[hi there]

thanks for the responses.

this code actually is for a report that runs on the "details section" OnFormat event.

therefore, i do not have the option of using the "OnLoad" or "OnCurrent" events.

 

[KeithG]

There is actually a conditional formatting feature built into Access. Have you tried using this?