Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Location
    Petersburg, Virginia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format on report by record and field (97/SR2)

    My report is based on a crosstab query showing staff (records/rows) and training (CPR, first aid, etc.) received (columns) with the date of training showing at the column/row intersection. I am trying to format the date field contingent upon a specified recency of training, e.g., if the date is less than a year old the date will show as black; if older than a year it will show as red. My efforts so far seem to change the field color FOR ALL RECORDS, not for each record selectively.

    I established a constant for green, and this code is in the detail band:

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

    If Val(txtdate) = 0 Then
    Me.txtdate.ForeColor = conGreen
    End If

    End Sub

    P.S. I am also having trouble showing the sum of "recent" dates at the bottom of the column.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Format on report by record and field (97/SR2)

    Hi,
    I think you need to add an else statement to your code to set the colour back to black:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    with me!txtdate
    If Val(txtdate) = 0 Then
    .ForeColor = conGreen
    else
    .forecolor = vbblack
    End If
    end with
    End Sub
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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