Conditional Format of Sub-report

lryckman · July 23rd, 2007, 02:12 PM

Hello!
I have the following code on a sub-report and isn't working correctly. Can anyone help? I need the Planned Completion Date field to show up in red shading and yellow text if the planned completion date is past and the Actual completion date is empty. Right now it tells me that an object is missing, but when I tried breaking this into a 'stepped' IF statement, then it evaluates and steps through the if statement correctly, but passes over the lines that I have to set the formatting. :(

Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.[7-ActualCompletionDate] Is Null And Me.[6-PlannedCompletonDate] < Now() Then
    Me.[6-PlannedCompletonDate].BackColor = vbRed
    Me.[6-PlannedCompletonDate].ForeColor = vbYellow
Else
    Me.[6-PlannedCompletonDate].BackColor = vbWhite
    Me.[6-PlannedCompletonDate].ForeColor = vbBlack
End If

End Sub

Regards,
Laura

FYI...My user id has changed. My old id was lbreitenbach

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

mmcdonal · July 24th, 2007, 07:14 AM

Try this on the Detail section's On Format event:

Dim dtDate As Date
dtDate = Now()

If IsNull(Me.7-ActualCompletionDate) Or Me.7-ActualCompletionDate = "" And Me.6-PlannedCompletonDate < dtDate Then
    Me.6-PlannedCompletonDate.BackColor = vbRed
    Me.6-PlannedCompletonDate.ForeColor = vbYellow
Else
    Me.6-PlannedCompletonDate.BackColor = vbWhite
    Me.6-PlannedCompletonDate.ForeColor = vbBlack
End If

Did that help?

mmcdonal

lryckman · July 24th, 2007, 09:23 AM

Mmcdonal,
Thank you very much for your assistance...that works perfectly!

Regards,
Laura

FYI...My user id has changed. My old id was lbreitenbach

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!