Conditional Formatting Null
By Richard Rost
5 years ago
Conditional Formatting on a NULL Value - Show Missing Data
In this video I'll show you how to use an expression to use conditional formatting to highlight Null values.
Madison from Cleveland, Ohio (a Gold Member) asks: I'm trying to use conditional formatting to highlight fields that are missing values, but it's not working. I've tried putting Null, Is Null, =Null, and a bunch of other values in, but nothing works. Help?
Members
I'll show you how to make that conditional format NOT apply to the new blank row on the bottom of the form.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Links
Continuous Forms: https://599cd.com/Continuous
Conditional Formatting: https://599cd.com/conditional
Conditional Formatting with Expressions: https://599cd.com/D1B3N
Conditional Formatting Show Duplicates: https://599cd.com/1444
Checklists (Also Covers Nulls): https://599cd.com/1447
 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 nulls, conditional format nulls, conditional format null values, isnull, blank values, missing values, missing data, expression is
Intro
In this video, we will look at how to use conditional formatting in Microsoft Access to highlight fields with missing or null values. I will show you how to set up rules so that empty fields such as first name or last name are automatically highlighted, either individually or across entire rows. We will talk about why common null value checks like "=null" or "is null" may not work as expected in conditional formatting, and I will demonstrate how to build effective expressions to identify and format these records.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost. In today's video, I am going to show you how to use conditional formatting but have null values activate the conditional formatting rule.
For example, if someone forgets to type in a last name or a first name, I will show you how to make the conditional formatting apply to the whole row if you want to, on just that value, or multiple values in the row. We will see how that works with null values.
Today's question comes from Madison from Cleveland, Ohio. Madison asks, "I am trying to use conditional formatting to highlight fields that are missing values, but it is not working. I have tried putting null, is null, equals null and a bunch of other values in, but nothing works. Help." I love when people use a question mark after help because I have to pronounce it like "Help? Can you help?" Yeah, certainly.
You cannot just use the word null or even the is null function when you are dealing with conditional formatting unless you know how to use expressions. Let me show you how that works.
Let's open up a copy of my blank customer database. You can download a free copy of this template off my website. I will put a link in the description down below the video.
Here I have my customer list, which is a list of all my customers. Let's say, for example, that some of these people did not put names in. I want conditional formatting to highlight those fields, maybe yellow, so I can quickly and easily see this. This works also with dates or number values or text or whatever you want to put in here.
So let's go to Design View: right-click, Design View. Let's go to the Design tab and go over to Format and click on the field that you want to highlight, and click on Conditional Formatting. This brings up this guy.
If you have never used conditional formatting before, I have other videos that teach you conditional formatting. Go watch those videos first. In fact, I have one that explains how to use expressions also. Go watch that one too. I will put links down below.
Now to find null values, you would think you could just go with New Rule, Field Value Is, set this to Equal To, and then put the word null over here. Then we will change the background color to, say, yellow. Hit OK. Hit OK. Let's save it and open it back up again - and it is not working.
OK, let's try something else. Design View. Let's go back into Conditional Formatting. What else could we try? Well, we could try putting =null in here. Let's see if that works. Hit OK. Oh, that is not even valid. How about is null, like that? Hit OK. Now that is not valid either. Maybe the is null function, like that? Now OK. So none of those things work.
That is because you cannot just put null in the field value is. What you have to do is use an expression. Go to Expression Is. In here, you put in a rule that pertains to the entire record. What you are going to put in here is something like is null - that is a function. So open up parentheses. Then put the field name, so in square brackets, [last name]. Make sure you put those square brackets in here. Usually you do not need them, but in this case, Access will put that inside a quote, which you do not want because then it will actually use the word last name instead of the field last name.
Now you could put equals true in here, but you do not need it because if it evaluates to true - if is null evaluates to true - the whole expression becomes true. So, there you go, just is null [last name]. I will zoom in better so you can see it clearly.
Now hit OK. Hit OK again. Save it and close it. Then open it up and look at that! Your null values, including the new record down there, will show up in yellow. That is because that expression pertains to the entire record.
Now if you want to copy that format to another field, like first name, you can do that with the format painter. Just go to Design View, click on Last Name, go to Format, find the Format Painter, and then hit First Name. That will format paint over onto the First Name. Now save it, close it, open up the list.
You might not get what you are expecting because remember, the rule is checking last name. So if any last name is null, first name and last name will show up in yellow. That might be what you want. You might also want to be able to check any field. So what you can do is, in Design View, you can highlight them both together if you want to, go into Format, Conditional Formatting, edit this rule. Right here you can say, OR is null [first name]. You can put any fields you want in here. You can use AND if you want to use AND instead of OR. That will be if both of them are null, you will see them highlighted.
Hit OK, hit OK, close it and save it. Open it back up again. Now if either one of these show up as null, both of them will highlight. See that? That is the OR condition. Now you can see anybody who is missing either a first name or a last name.
Or you can do them individually. You can say this one will just highlight yellow if it is first name, and this one will just highlight yellow if it is last name, whatever you want. That is the beauty of Access. That is why you build your own database, you can do whatever you want.
So that is all. Of course, if you have any questions, post down below in the comments section.
Want to learn more? One more trick in the extended cut for the members: I do not want to see that blank new row down at the bottom showing up as having missing information. That is annoying. These guys up here, if I am missing some stuff, sure, I want that row to highlight, but I do not want to always see that yellow down here, especially if you are being even more annoying with the colors and you are going with red.
So in the extended cut, I will show you how to not have that conditional formatting rule apply on your blank new record on the bottom. That way, you do not have to see that down there. As soon as you start typing in, the row will highlight, but then when you are done, if you put both fields in, it goes away. See that? That is in the extended cut for members.
Once again, that is the conditional formatting nulls members only extended cut. Silver members and up get access to all of my extended cut videos, and we are approaching 100 now. So there is lots of material to watch.
How do you become a member? Click on the Join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks.
But do not worry. These TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more, and they will always be free.
Quiz
Q1. What is the correct way to apply conditional formatting in Access when you want to highlight fields with null values?
A. Use the Field Value Is option and enter the word null
B. Use the Field Value Is option and enter is null
C. Use the Expression Is option with the is null function and the field name in square brackets
D. Use the Field Value Is option and enter =null
Q2. In Access conditional formatting, why should you use square brackets around a field name in expressions?
A. To refer to the field by its display name
B. To ensure Access does not interpret it as a string literal
C. To avoid syntax errors caused by missing parentheses
D. To make the formatting apply to all rows
Q3. What happens if you use the format painter to copy a conditional formatting rule that checks one field (such as [last name]) to another field (such as [first name])?
A. The formatting only applies to the new field
B. The rule automatically updates to check the new field
C. Both fields will highlight based on the original field's value
D. The rule is deleted for both fields
Q4. How can you adjust your conditional formatting rule to highlight fields if either "first name" or "last name" is null?
A. Use the AND operator in the expression
B. Create separate rules for each field without any logic operator
C. Use the OR operator with both is null checks for each field
D. Set the Field Value Is option to null for both fields
Q5. If you want the conditional formatting to highlight only when both "first name" and "last name" are null, which logical operator should you use?
A. OR
B. AND
C. NOT
D. XOR
Q6. What is a limitation of conditional formatting with nulls in Access as described in the video?
A. It cannot be applied to text fields
B. It cannot highlight the whole row based on one field
C. It will also highlight the blank new record row at the bottom by default
D. It only works for number fields
Q7. What additional feature was promised in the extended cut for members regarding conditional formatting?
A. How to highlight all records regardless of their values
B. How to prevent formatting from applying to the new record row at the bottom
C. How to apply formatting to forms only
D. How to make formatting permanent
Answers: 1-C; 2-B; 3-C; 4-C; 5-B; 6-C; 7-B
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 covers how to use conditional formatting in Microsoft Access to highlight fields that have missing, or null, values. This tool is especially helpful when you want to quickly identify records where important information, such as a first or last name, is absent.
The question I am answering today is about why typical approaches to conditional formatting for null values do not always work as expected. For example, many users attempt to use "null," "is null," or similar entries when building conditional formatting rules, but find that nothing happens. This issue comes up frequently, so it is important to understand how Access evaluates these conditions.
First, I start by demonstrating using a sample customer database that you can also download from my website. In this customer list, suppose a user forgets to enter a first or last name for some records. I show how to use conditional formatting to make missing values stand out, such as by changing the background color to yellow.
To access conditional formatting, switch the form or datasheet to Design View, then select the field you want to highlight. Open Conditional Formatting from the Format tab. Many people try to create a new rule using "Field Value Is," select "Equal To," and enter "null" as the value. You might expect this to work, but it actually does not. Other attempts, like using "=null" or "is null," are also invalid. The problem is that Access does not recognize "null" in these contexts.
The solution is to use the "Expression Is" option. When you create a rule this way, you can use the IsNull function. In the expression box, enter IsNull with the field name in square brackets, such as IsNull([LastName]). You do not have to compare this to True, because the function already yields a true or false result. Once you apply this rule, any record where the last name is missing will be highlighted in the color you selected.
If you want to use the same formatting rule for another field, like FirstName, you can use the format painter to copy the formatting. Keep in mind, though, if you set both fields to use the same rule checking for a null last name, both fields will highlight even if only the last name is missing. To check for either field being null, edit your rule to include the OR operator, such as IsNull([LastName]) OR IsNull([FirstName]). This way, if either the first name or last name is missing, the conditional formatting will apply. You can also use AND if you only want the formatting to activate when both fields are missing.
Access gives you the flexibility to set these rules either for individual fields or for combinations of fields. That is one of the advantages of building your own database.
If you want more control, there are additional tricks covered in the Extended Cut for this lesson. There, I explain how to prevent conditional formatting from being applied to the blank new row at the bottom of the data sheet. This can be useful so that the new, empty record does not always appear highlighted. In the Extended Cut, I show you how to set up your rule so that the conditional formatting only activates after you start entering data, and disappears once all required fields have values.
Remember, Silver members and above have access to all my extended cut videos, additional lessons, and live chat sessions. You can find details about membership levels and their benefits on my website.
For everyone else, do not worry. These TechHelp videos are free and will continue to be available. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.
Topic List
Using conditional formatting with null values in Access
Highlighting fields missing values using conditional formatting
Setting up conditional formatting for individual fields
Applying conditional formatting to multiple fields or entire rows
Using 'Expression Is' for conditional formatting rules
Writing expressions using IsNull for field evaluation
Copying formatting between fields with the Format Painter
Combining multiple fields in one conditional formatting rule using OR and AND
|
