Expression Is
By Richard Rost
4 years ago
Conditional Formatting Expression Is Option
In this Microsoft Access tutorial, I'm going to teach you how to use the Conditional Formatting Expression Is option to color notes red if the field starts with an asterisk (*).
Gary from Portland, Oregon (a Gold Member) asks: We store a lot of different things in our customer notes field. However if something is critically important, is there a way to make that notes field turn red or something so my reps don't miss it?
Pre-Requisites
Links
Recommended Courses
 Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, conditional formatting based on contents of field
Subscribe to Expression Is
Get notifications when this page is updated
Intro In this video, I will show you how to use the "expression is" option in conditional formatting to highlight a notes field in Microsoft Access when it starts with an asterisk, making it easy to spot important information. You will learn how to set up the conditional formatting rule, use the LEFT string function to evaluate the first character of the notes field, and resolve common issues like needing square brackets for field names. This technique helps ensure critical notes stand out in your database forms.Transcript In today's video, I am going to show you how to use the conditional formatting "expression is" option to color your notes field red if it starts with an asterisk. If you just have normal notes, it will look yellow like that, but if you put an asterisk at the front of it, it will look red like that so you do not miss important stuff.
Today's question comes from Gary in Portland, Oregon, one of my Gold members. Gary says, "We store a lot of different things in our customer notes field. However, if something is critically important, is there a way to make that notes field turn red or something so my reps do not miss it?" Yes, Gary, this is a trick that I have used myself in my personal database for years.
If something is really important and you want to make sure your reps do not miss it when they go to that customer's account, have it turn red. You can even make it flash if you want to, but that is a different video.
Let me show you what I mean. Here I opened up James Kirk's record and it is just a normal note. But if I go to my record, oh, look at that. It turned red so that you are guaranteed not to miss that. The way that I do it is I simply put an asterisk as the first character in that notes field. If I put an asterisk there, it turns red. We can do that with a little bit of conditional formatting, no programming whatsoever.
Could you do it with a checkbox? Yes, you could put a little checkbox over here if you want to, but you have to add another field to your table. It is easier to put a little star there.
Let me show you how I did that. First, if you have never done any conditional formatting before, go watch this video first. You have to understand what conditional formatting is and how to use it before I can show you what I am going to show you today. Also, go watch my strings function video. We are going to use the left string function to determine what that left one character is. If you do not know any of these left, right, mid functions, you really need left for this example, so go watch this video too. These are free. They are on my website and my YouTube channel. Go watch them and come on back.
Here I am at my TechHelp free template. This is a free database. You can also grab a copy of this off my website if you want to as well. In my customer form, I have this notes field.
As you know, using regular conditional formatting, you can say things like if the family size is greater than five, make that field blue. If the credit limit is less than zero, make that field red. How do you do it with just a part of that? We want to look at the left one character of that field and say if there is an asterisk there, then I want to color this thing red.
Let's go to design view. Now, we are going to click on the notes field, go to format, and then conditional formatting.
Now, new rule. There is "field value is," and you have a bunch of options in here: between, not between, equal to, greater than, less than. But there is no includes, is part of it. They should add that, honestly. Microsoft, if you are listening, put something like "is included in" in here so you can have any part of a string instead of having to do what I am showing you how to do now.
We have to use "expression is." Now, "expression is" can be all kinds of different stuff. You can even base this on other fields if you want to. I cover how to do that in my Access Expert 25 class and in the extended cut for the regular conditional formatting video, but I am going to show you one trick right now.
We are going to come in here, and I am going to say if the left one character of the notes field is equal to an asterisk, then apply this format. I am going to say LEFT and then parenthesis NOTES, 1 equals and then quotes, the asterisk. I cannot zoom in. Actually, I could zoom in here with the recording software. There you go. The Shift-F2 zoom in does not work in this.
The LEFT NOTES, 1, if that is equal to an asterisk, then we are going to apply the format. What format? Make the background red and make the foreground white. Then I will hit OK. Then I will hit OK again.
Now, take a look at what that looks like right there, by the way. Just put it in the back of your brain. Hit OK again. Save it. Let's close it. Open it back up again. Put an asterisk there, hit Tab, and it is not happening. What is going on? Why is it not working?
This comes up a lot. I leave this mistake in here intentionally because I make this mistake still after using Access for almost 30 years. I still goof this one up every now and then. Go back into your conditional formatting. Take a look at exactly what happened here. Let me zoom in again so you can see it.
Do you see it? So Access is trying to be helpful and it puts NOTES inside of quotation marks. That turns that into the string "Notes." We do not want that. We want the field NOTES, the field values. We have to put that inside of square brackets. I know in my Access Beginner 1 class, I teach you that as long as you do not have spaces in your field names, you do not need those brackets. That is true 90 percent of the time, maybe 98 percent of the time. But once in a while, especially here in conditional formatting, and I hate this, Access will treat that as a string and put quotes around it.
Let's edit that rule, double click on it, and change that to NOTES inside of square brackets. It is one of the rare times you actually have to use the square brackets if you do not have spaces in your field names.
Hit OK. Hit OK. Save it. Close it. Let's open it up again. And there it is. Voila. See? Is that not much nicer?
Go to somebody else, go back. Perfect. Now it is working. The left one character, and you can make that whatever character you want. It does not have to be an asterisk. It could be whatever you want. You can make it two characters if you want. You can have it search for something inside the entire string using the INSTR function that I mentioned earlier. Again, I covered that in my string functions video. Go watch that for more information on INSTR.
If you want to learn a lot more about this conditional formatting, and especially this expression is, I cover more of it in my Access Expert Level 19 class. That covers conditional formatting, advanced conditional expressions, all kinds of stuff. Down here, I cover that INSTR example and more with conditional formatting, data bars, all this stuff.
I also cover a little bit more with the expressions in conditional formatting in the extended cut for the original conditional formatting video. That is for the members. We will talk about this in a little more detail.
As I mentioned earlier, you can also use conditional formatting for flashing text. I have a video for that. So if it is super urgent and you want it to flash red back and forth, go watch this video.
If you want to learn a lot more about those string functions, I cover them in depth in Access Expert 25, which covers all the string functions, pretty much all of them. We also go into logical functions. This whole group of classes, 25, 26, 27, is all my comprehensive guide to Access functions. I cover all the functions you could want. There are math functions, type conversion functions, date time functions - there are tons of them. That is why I recommend my full courses if you want to learn this stuff in depth and not just little pieces here and there, which is what the Fast Tips videos are for.
So Gary, there you go. I hope that answers your question. I hope you learned something today. I will see you next time.Quiz Q1. What is the main purpose of using an asterisk at the beginning of the notes field?
A. To indicate the note is important and should be highlighted red
B. To sort notes alphabetically
C. To make notes field searchable
D. To prevent users from editing notes
Q2. Which feature in Microsoft Access is used to change the background color of the notes field based on its content?
A. Table relationship
B. Conditional formatting
C. Primary key setting
D. Data validation rule
Q3. What is the function of the "expression is" option in conditional formatting?
A. To format fields only when they are empty
B. To apply formatting based on a custom logical expression
C. To sort data in a field
D. To automatically save records
Q4. Which function is used to check if the first character in the notes field is an asterisk?
A. RIGHT
B. MID
C. LEFT
D. FIND
Q5. When using conditional formatting in Access, why must the field name be enclosed in square brackets in the expression?
A. Access always requires brackets for all field names
B. To reference a field and not a literal string value
C. Because field names cannot have spaces
D. To enable case sensitivity
Q6. What mistake did the video intentionally leave in to demonstrate a common error?
A. Using single quotes instead of double quotes
B. Forgetting to save the form after changes
C. Letting Access automatically put the field name inside quotation marks, making it a string
D. Applying the format before entering any data
Q7. Besides the asterisk, what else can be used with this technique to trigger formatting?
A. Only numbers can be used
B. Any character or even an entire string pattern
C. Only lowercase letters
D. Only the word "IMPORTANT"
Q8. According to the video, what could you use instead of this string method to indicate important notes, though it is more complex?
A. Adding a checkbox field
B. Using a formula in Excel
C. Sorting the table by date
D. Printing the notes separately
Q9. What additional function was briefly mentioned for searching inside the entire note, not just the first character?
A. SUM
B. LEN
C. INSTR
D. UCASE
Q10. Where can you find more detailed lessons about string functions and conditional formatting as suggested in the video?
A. Access Beginner 1 class only
B. The Fast Tips videos exclusively
C. Access Expert 19 and 25 classes, and extended cut member videos
D. The Access startup wizard
Answers: 1-A; 2-B; 3-B; 4-C; 5-B; 6-C; 7-B; 8-A; 9-C; 10-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on how to highlight critical notes in your Access database using the conditional formatting feature with the "expression is" option. This technique is especially helpful if you want certain important notes to stand out so your staff never overlooks them.
The question for this lesson originates from a user who manages various pieces of information in the customer notes field. He wanted a way to make crucial notes visibly stand out, such as by turning the background red, ensuring that his representatives do not miss vital information. My own experience with Access has shown how useful this approach can be.
Whenever there is an urgent note, I use a simple trick: adding an asterisk at the beginning of the notes field transforms the background color to red. Leaving the notes field as usual results in a standard yellow background. That visual difference instantly signals the importance of the message. This method uses conditional formatting — there is no need for programming or adding extra fields like a checkbox, which would require modifying the table.
Setting this up in Access is straightforward. If you have not worked with conditional formatting before, I suggest reviewing my introductory lessons on that topic first, along with a video about string functions, especially the use of the Left function to work with text data. These resources will prepare you for this process.
Using the free TechHelp template database as an example, I demonstrate how to highlight important notes. Normally, conditional formatting rules are applied to numeric fields, such as setting a credit limit field to red if it falls below zero. However, in this situation, we need to examine part of a text field — specifically, we want to check if the first character is an asterisk and apply the new color formatting in that case.
To implement this, switch to the design view of your form and select the notes field. Access provides several types of conditional formatting rules, but there is no direct "contains" option for partial string checks. That is why the "expression is" option becomes necessary here. With "expression is," you can base your formatting on more complex criteria, sometimes even referencing other fields.
The setup involves writing a conditional expression that checks whether the first character of the notes field is an asterisk. This is accomplished using the Left function. The expression looks at the first character and tests it against the asterisk. If it matches, the formatting rule changes the background to red and the text color to white.
A common pitfall here is Access sometimes tries to be helpful by placing your field name in quotation marks. This causes Access to treat it as a literal string rather than a reference to your notes field. In conditional formatting expressions, you must wrap the field name in square brackets to make it work correctly. Even if your field name does not include spaces and normally would not need brackets, in this context it is necessary. Once you correct this by replacing the quotes with square brackets, your rule should work as expected.
After fixing the rule, you will notice the notes field turns red whenever there is an asterisk at the front. The character used as a trigger is up to you; it does not have to be an asterisk, and you can even search for different patterns if needed, such as by using the InStr function. I discuss more advanced uses of conditional formatting like this in my Access Expert Level 19 course, including examples involving searching for string fragments.
There are even more possibilities with conditional formatting, like making text flash to indicate truly urgent items. I cover techniques like that in a separate tutorial, so check that out if it interests you.
If you would like to improve your understanding of string functions such as Left, Right, Mid, and InStr, I recommend reviewing my comprehensive Access Expert 25 course. Altogether, the series offers broad coverage of functions in Access, including logical, math, date/time, and type conversion functions.
I hope this explanation helps clarify how to flag important notes for your staff using simple conditional formatting in Access. For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below.
Live long and prosper, my friends.Topic List Using conditional formatting "expression is" option
Identifying if a notes field starts with an asterisk
Applying different background colors based on field value
Using the LEFT string function in Access
Writing conditional expressions for formatting
Editing conditional formatting rules in design view
Correct syntax for referencing fields in expressions
Understanding when to use square brackets in field names
Troubleshooting common errors with conditional formatting
Testing and saving conditional formatting changes |
