Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Posts
    37

    Unanswered: Color of background behind text in report based on criteria

    I have a report that I would like to have color coded one of 3 colors if a combination of fields meets a certain criteria. What I'm tring to accomplish is to place a rectangle behind two fields in the report, and if a combination of two other fields meets a certain criteria (field 3 = x and field 4 = y, for example), then the background would be red, otherwise it would have no color unless it met one of the other two conditions. In other words, I'm trying to replicate what I would do in Excel under conditional formatting where the cell backgrounds for the first two records would be red if the other cells met the specified criteria, would be yellow if it met other criteria (field 3 = x and field 4 = z, etc). Is this possible? Thanks.

  2. #2
    Join Date
    Sep 2005
    Posts
    37
    After looking at my report, I realized that it would be better to scrap the rectangle and use conditional formatting of the fields. However, the expressions in the conditional formatting would be based on criteria of another field, i.e., the background of field 1 would be red if field 3 = x and field 4 = y. I'm somewhat familiar with writing expressions, but not sure how to do this. Thoughts?

  3. #3
    Join Date
    Sep 2005
    Posts
    37
    After rethinking how I wanted to do this, I was able to accomplish what I needed by creating an expression in conditional formatting. Played around with it for a few minutes and got it to work.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    I'm gald to hear you got it working. But if you still need help, here's an article on shading every other row of a report gray; the concept is similar.
    Me.Geek = True

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ....the alternative is to place the code in the reports on detail format event. that will allow you to do what ever you wish. it will also allwo you to makew changes to multiple controls at a single IF, allow you to have more than 4 colours/fill combinations
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2005
    Posts
    37
    Healdem...that would probably be best. Right now, I'm only using 3 conditions, but I'm sure that as I develop my db I'll need more. I'm not that good at writing code. What should the code look like?

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    select case logical test
    case a:
    backgroundcolour=vbred
    foregroundcolour=vbwhite
    fillstyle= balh di bah
    case b:
    backgroundcolour=vbblue
    foregroundcolour=vbblack
    fillstyle= balh di bah
    ...y'get the picture
    case else
    foregroundcolour=vbbblack
    backgroundcolour=vbwhite
    fillstyle=vbnull
    end select'then set whatever controls you need to
    mycontrol1.forecolor=foregroundcolour
    mycontrol1.backcolor=backgroundcolour
    mycontrol1......
    mycontrolx.forecolor=foregroundcolour
    mycontrolx.backcolor=backgroundcolour
    mycontrolx......


    code goes int he report detail on format event
    always always make sure you set the colours each time you go through the format, otherwise the previous format will be retained
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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