Change Back Color of Rectangle in Query

ttist25
ttist25 used Ask the Experts™
on
Hello,

I have a microsoft access 2007 database with a report fed by a query.  I'm using iif statements to get values from the record and display text in text fields.

I would like to also use iif statements to change the Back Color of a rectangle on my report based on the value of a field.  

Here's an example of what I'm doing to display text:

iif(myTable.MyField_Description = "Value1",
"Text for value1.",
"Text for everything else.")
AS myTextBox,

That will display the appropriate text in myTextBox.

I would like to use the same thing to change the back color of a rectangle but I'm not sure of the syntax.  It would be something like this:

iif(myTable.MyField_Description = "Value1",
my.Rectangle.BackColor="#000000",
my.Rectangle.BackColor="#FFFFFF")
AS myRectangle,

FYI - if you haven't figured it out by now, I need to point out that I'm a serious noob with this stuff.  

TIA!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Have you tried using conditional formatting?  Don't think that will actually work for a "rectangle", but you could create a textbox for the [MyField_Description] on the report, then use conditional formatting to test if the fieldvalue = "Value1" and if so use a conditional format.
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Unfortunately conditional formats are limited to a default and 3 formats, so if you want to go beyond 3 colors, you would be best to use the Format event of the section in which the control is located.  for just 2 conditions, the syntax might look like:

Private Sub Detail_Format

    me.Rectangle.Backcolor = iif(me.MyField_Description = "Value1", rgb(0,0,0), rgb(255, 255, 255))

End if

Hamed NasrRetired IT Professional
Commented:
Try: for field f1, box box11
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If f1 > 5 Then
        Box11.BackColor = vbRed
    Else
        Box11.BackColor = vbBlue
    End If
End Sub
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Ok - that gives me good ideas on the syntax of changing the color but, is it possible to do this inside my query without creating a sub?
Hamed NasrRetired IT Professional

Commented:
"is it possible to do this inside my query without creating a sub? "

A query is like a table that displays data. I can't see a way to use a table to change bacground color without using an action statement.

The sub is there in the report, you only add the action statement to set the color on condition.

Author

Commented:
Definitely not the most graceful solution I'm sure but, here's what I ended up doing:

I replaced the rectangle with a text box of the same size and shape
I used my iif statement as described above and set the text to either "On" or "Off"
Then I used the Conditional formatting toolbar thing to set the background fill and foreground font accordingly (either both gray or both white)

Works great!  Thanks for the help.  
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Glad I could help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial