Avatar of ttist25
 asked on

Change Back Color of Rectangle in Query


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",
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.  

Microsoft AccessSQL

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Dale Fye

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.
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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 Nasr

"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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

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 Fye

Glad I could help.