Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004

    Unanswered: format cross tab

    I have a set of data that I would like to format into a cross tab, but use some colors. The data should look a bit like this:

    5/24 5/25 5/26
    ID1 Desc 0% 100% 75%
    ID2 Desc 25% 0% 100%

    (formating didn't work...see two rows (ID1 and ID2) and three columns (5/24, 5/25, 5/26)

    Only I want the 0%, 25%, 75%, or 100% to be different colors (ie. red, orange, yellow, and green) for the different levels of completion.

    I have no problem getting a cross tab query to just put the data in this format. And I know how to put it into a report so I can add color, however that only works if the dates always stay the same. The dates will always be changing, and may be an input for the query, so I don't know how to make it work (why won't it just work?!?!?). Does anyone have a suggestion on how to make this work? Please help, I'm at a loss.

    Let me know if I can better describe what I'm looking for and thanks for the help!

  2. #2
    Join Date
    Feb 2004
    In the report detail on_format event do something like this
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    select case Me.test
    Case 0
    Me!test.ForeColor = vbRed
    case 0.25
    Me!test.ForeColor = vbBlue
    case = 0.5 Then
    Me!test.ForeColor = vbBlack
    Case = 0.75 Then
    Me!test.ForeColor = vbYellow
    Case else
    Me!test.ForeColor = vbGreen
    end select
    End Sub

    On some later versions of Access there is a format switch for this function - see help for this idea.

    You can use system colors to get a numeric value for orange as ther is no intrinsic constant for orange.

Posting Permissions

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