Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Unanswered: changing font color in report date field based on age of date

    Someone gave me some help with this question and gave me some code, but now I can't reach them to get answers to some questions I have.....Anyone willing to give it a try?

    How would I have the program change the color or bold the date in the date field of a report if that date is greater than 45 days old?

    This is the response that I was given:
    In the On Format event for the report band use something like :

    If DateAdd("y",Me!datefield,Now()) > 45 Then
    Me!controlname.BackColor = Red
    Me!controlname.BackColor = White
    End If

    Here are the questions I have :

    If the date field that I want to change is DT_LAST_PAY Would I insert it where you have DateAdd ?

    Will this change the backround color or the font color?

    I would like to change the font color to red when the date in the DT_LAST_PAY field is greater than 45 days less than todays date.

  2. #2
    Join Date
    Feb 2004
    A quick and eazy way to do color changes based on some criteria would be to create a field in the report query that calculates the aging days and alias (name) it something meaningful to the end user. enter this calculation in the query DaysPastDue: Datediff("d",Now(),[YourFieldName])+45

    What this will do is calculate the days over or under the 45 day cut-off
    Positive values indicate days before the pastdue date and negative numbers indicate the number of days pastdue or past that 45 day period.

    In the report add this text box next to the date control and set it's control source to this calculated field. In this control's format property use something like the following "Current";[Red]"Past Due","Current"

    What this will do is on positive values it will display "Current" (or some other text you might want next to the date field. Negative values will show the red text and zeros will show the third value. Using the format property makes sure only that textbox gets the color change.

    A bit verbose but this will work in all versions of Access.

  3. #3
    Join Date
    Nov 2003
    Place this code into the On Format event for the Detail section of your report (OR for the report section that is home for your DT_LAST_PAY date field):

    If DateAdd("y",Me.DT_LAST_PAY,Now()) > 45 Then
       'Change the Font color to Red.
       Me.DT_LAST_PAY.ForeColor = vbRed
       'Change the Font Weight to Bold.
       Me.DT_LAST_PAY.FoontWeight = 700
       'Change the Font color back to Black.
       Me.CDT_LAST_PAY.ForeColor = vbBlack
       'Change the Font Weight to Normal.
       Me.DT_LAST_PAY.FontWeight = 400
    End If
    Hope this helps

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts