Conditional Formatting: Background Color Rules for Only a Specific List of Text?

[warrenpage]

Hello, everyone, I need a bit of help with conditional formatting with Access 2013.

I know how to use conditional formatting to change the background color of a text box, depending on the text entered into it.

For example, if I type BLUE into the text box, the background color will change to blue.

If I type RED into the text box, the background color will change to red.

Here is a list of the values that can be entered into this text box:

1. Blue
2. Green
3. Orange
4. Purple
5. Red
6. White
7. Yellow

Not all of the records will have text entered into this text box. In other words, the text box will be blank for some records.

Here's what I want to do: I want the text box background to turn totally black if the above values are not entered. For example, if one of the seven values is misspelled, I want the text box to turn black. If a number, symbol, or anything else other than these seven values is entered, the text box should turn black.



I tried creating seven NOT EQUAL TO rules, such as Field Value is not equal to Red.

That worked, but the problem is this: The text box turns black for the records with nothing entered in the text box.

I haven't figured out a workaround for this. If there is nothing entered at all in the text box, I want no change. I do not want the text box to turn black.

I tried Equal To " " and Equal To "" but that did not work.

How do I accomplish this? I hope I explained my problem adequately. Thank you! Warren Page

PS: A .zip file with the database is attached to this message.

movies.zip

[ItsMe]

Have you tried
If IsNull(FieldName) Then

[June7]

You want the textbox to turn black if there is something other than one of the color words?

Remove all the conditions for black as well as the one for empty string that is white color.

Replace with:

Field Value Is not equal to "" (the resulting expression will be: Value <> "")

Make it the last condition and set it for black background. Might also want to set the text as white.

[warrenpage]

You want the textbox to turn black if there is something other than one of the color words?

Yes and no. Like I said in the original post, I want the text box to turn black if there is something in there other than one of the color words, yes.

But...I want it to do NOTHING if there is nothing at all in the text box. That's where I'm running into the problem.

Field Value Is not equal to "" (the resulting expression will be: Value <> "")

I did that, and that didn't work. Well, it did turn the field black if the text wasn't one of the seven words, but if the field had a null value, it was black, as well.

I need to solve the problem of the null fields. I don't know how.

[warrenpage]

If IsNull(FieldName) Then

How do I use this?

[ItsMe]

How do I use this?

You replace FieldName with the name of the control you are checking to see if it is Null.

[warrenpage]

No no...sorry, I meant is this part of a macro, or VBA code, or can it be used in Conditional Formatting?

[June7]

My suggestion in post 3 worked for me when I tested it with your db. If one of the color words was in the box, the associated color displayed, if there was something else in the box, it turned black, if box was Null then it stayed the default white.

Did you delete the conditions as instructed?

I always set text field Allow Zero Length property to No so there can never be an empty string in the field.

Why do you need MovieID in the Location combobox list? Consider:

SELECT DISTINCT [tblMovies].[Location] FROM tblMovies ORDER BY [Location];

Or should Location be a specific list of choices like you have for Language?

[warrenpage]

Then I'm clearing doing something wrong, or not understanding.

How many rules, total, do you have in your test?

My interpretation of your post was a total of eight. Can you post your revision of my db, please?

[June7]

All I did was delete the black rules and the one for the empty string. Then created a new one at the end of the conditions. For a total of 8 conditions, not counting the default.

As I navigate the records I see color change. 88 Minutes is the first one with Null and the DVDColor textbox is just the default white. You might not want to use white as one of the color conditions.

Post your attempt for analysis Let's see if I see what you see. I hope the issue is not with 2013 - I am using 2010.

As you were posting, I edited my previous post for some other comments.

[warrenpage]

Did you delete the conditions as instructed?

Yes, but I'm not 100% sure what to replace it with. Either you were not specific enough, or else I did not fully understand.

I always set text field Allow Zero Length property to No so there can never be an empty string in the field.

Not all of my movies are color-coded, so I either have to allow that field to be blank, or else come up with something to act as a placeholder for the non-color movies. I have to decide which might be preferable.

Why do you need MovieID in the Location combobox list? Consider:

SELECT DISTINCT [tblMovies].[Location] FROM tblMovies ORDER BY [Location];

Movie ID should definitely not be in the Location combo box list. If you see a number in that list (I see a 162), then it is a mistake. I am not familiar with SELECT DISTINCT and do not yet understand what that sequence is supposed to do.

Or should Location be a specific list of choices like you have for Language?

I can tell you there should be only four options in that combo box, but I choose not to use an option group because Access' storing numbers in the table instead of text drives me absolutely stark-raving-climb-the-walls crazy. FileMaker Pro doesn't do this.

You might not want to use white as one of the color conditions.

Sorry, I have to, because several movies are contained in white sleeves.

This movie database isn't primarily about the movies--it's about where I can find them in my house. That's one of the reasons why I color-coded the movies.

If I need to add only one more rule to the Conditional Formatting, then I really just want to see what that rule looks like when completed.

[June7]

I deleted the conditions as described then added new one: Field Value Is not equal to ""

Just choose not equal to from the list of condition choices then type "" (that is two sequential quotation marks, nothing between them) in the expression box. How can I be simpler and clearer?

Allow Zero Length set to No will still allow blank fields - the blanks will be Nulls, never empty string. Null and empty string are not the same thing.

DISTINCT does what the word means - returns unique values from field so values are not repeated in the combobox list.

I did not suggest an option group instead of combobox for Locations, I suggested a Value List as you have with Language, however, the DISTINCT query will achieve the same result, as long as each value is used at least once. But since you have the combobox Limit To List set to yes, it will be difficult to enter data to this field starting with an empty table.

Maybe you should make a light grey the default color.

[warrenpage]

I got the conditional formatting working.

Also, somehow I managed to figure out how to plug the SELECT DISTINCT SQL statement into a query. I tried making a combo box based on the query, and bind it to the Location field. It worked. Very interesting. It just required a few minutes of experimenting.

I also tried the same thing with the DVD Color field, and it worked, as well. However, after creating a DVD Color combo box, I noticed a blank option was part of the list. I don't particularly care for blank options in combo boxes or list boxes, so I think I'm going to find/replace empty records in the field with NONE or NO COLOR. That would also help simplify the conditional formatting. I think NONE will be my ultimate choice. What is your opinion of that?

Now that I understand where to place SQL commands in a query, I think I'm going to work harder to understand SQL basics. I have a couple of e-books about SQL for beginners, but haven't really hunkered down and tried studying them seriously. Now might be a good time. It seems SQL can really expand a database's capabilities.

One final comment: I know you did not mention option groups. I brought the subject up, in the event you or someone else asked me why I don't go with one.

Thanks again for your help.

[June7]

Requiring a value for every record is one approach, another is:

SELECT DISTINCT [tblMovies].[Location] FROM tblMovies WHERE Not Location Is Null ORDER BY [Location];

[warrenpage]

Requiring a value for every record is one approach, another is:

SELECT DISTINCT [tblMovies].[Location] FROM tblMovies WHERE Not Location Is Null ORDER BY [Location];

I plugged in this SQL sequence into my DVDColor query, substituting DVDColor for Location. I saved the query, ran it, and there is one blank space at the beginning of both the query and the combo box I made from this query. That's fine, it did what it was supposed to do.

By the way...thanks for the SQL sequences. Each one I get helps me learn and understand SQL a little bit. I was thrown off by the WHERE NOT part, though. I'm still trying to figure that part out.