Change lable and text control based on value

Posted on 2011-10-13
Last Modified: 2012-05-12
I have a user request that if the due date is less than Today plus 7 days, they want the lable and text control to appear on the report as red.  I tried the below in the group format, but it turns all lables and due date text controls to red.  This is an Access 2003 report.
Private Sub GroupHeader2_Format(Cancel As Integer, FormatCount As Integer)
'If Due date is with 7 or less days, make the box and data red

If Me.TaskDueDate <= (Date + 7) Then
     Me.lblTaskDueDate.BorderColor = RGB(239, 5, 29)
     Me.lblTaskDueDate.ForeColor = RGB(239, 5, 29)
     Me.lblTaskDueDate.FontBold = True
     Me.TaskDueDate.BorderColor = RGB(239, 5, 29)
     Me.TaskDueDate.ForeColor = RGB(239, 5, 29)
     Me.TaskDueDate.FontBold = True

End If

End Sub

Open in new window

Question by:ssmith94015
    LVL 61

    Accepted Solution

    Try using "Conditional Formatting" instead of code.

    Right-click a text box, and select Conditional Formatting.

    Then, select "Expression Is" for the formatting type.

    In the textbox provided, place the following:
    [TaskDueDate] <= Date() + 7

    And select the color you want for the formatting
    LVL 61

    Expert Comment

    Not sure if you can conditionally format a label.  If you cant, you can always use a borderless texbox instead of a label on your report and get the same effect.

    Author Closing Comment

    Actually, I just asked that very question as I have two conditions that need to be met.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Suggested Solutions

    A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
    The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now