|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Null By Richard Rost 
4 years ago
How to use Is Null, Is Not Null, IsNull, & Not IsNull In this video, I'm going to teach you what Null means in Microsoft Access. We'll see how to use Is Null, Is Not Null, IsNull(), and Not IsNull(). We'll look at query criteria, functions, and VBA code. Links
Suggested Courses
Keywordsaccess 2016, access 2019, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, null, isnull, is null, not isnull, is not null, ms access what is null, IsNull Function, data is missing or unknown, criteria, vba functions
IntroIn this video, we'll talk about what null means in Microsoft Access and how to work with null values in your tables and queries. I'll show you how to use the "is null" and "is not null" criteria to find records with missing or existing data, explain the IsNull function for queries, forms, and VBA code, and demonstrate practical examples, like using calculated fields to handle nulls or highlighting missing fields in your forms. If you're curious about finding and working with blank or missing data in Access, this video will help you understand how it all works.TranscriptWelcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In this video, we're going to talk about what null means in Microsoft Access.In Access, a value is null if it contains no valid data. The data is missing or unknown. For example, here you can see both Jean-Luc and Malcolm have null values. They're missing data in their address and city fields. Generally, if you don't specify a default value for a field and you don't type something in yourself, that value is going to be null. We can use a query to find those values by putting "is null" in the criteria row. See, criteria: is null. If you don't know how to use query criteria, watch my other video on it. I'll put a link down below in the description. Go watch that. This query will find all customers who have a null address and a null city field. I talk about how to change between the AND and OR condition in my Access Beginner Level 5 class. When we run this query, we can see that the query returns only Jean-Luc and Malcolm. They both have null address and city fields. Now, let's say you're sending out a mailing and you want to show the customers who have data in their address and city fields. You can include state and zip code and all that too. You can use "is not null" to make sure you get values that have data in them. You put "is not null" right there. Now, when we run that query, you can see we have all the customers who have values for address and city. In addition to the "is null" criteria words, we've also got an IsNull function - it's one word. You can use the IsNull function to check and see if a field is null. You can use this in your queries, and you can also use this directly in your forms and in your VBA code. This function will return a true or false value if that field is null. This is great for using with other functions, for example, the Immediate If function (iif). If you don't know how to use that, go watch my video on it. Here's an example. This creates a calculated query field called n, and it says if x is null set n equal to y; if not, set it equal to z. If you don't know what calculated query fields are, go watch that video. Here's a perfect example. In my databases, I don't usually enter a country value for my customers in the United States because I'm in the United States, so that's just my default. Blank means USA. So if you want to make a calculated field showing "USA" in place of a null value, you can use the iif function with IsNull. Here's the actual country field out of the customer table. I create a calculated field called Country2, and it says if IsNull(Country), then set Country2 equal to "USA"; otherwise put the country field there. Here are the results. You can see, for example, these are blank and they get turned into USA. These have values, so it uses those. There you go. Yes, I know, don't send me the hate mail. Betazed is a planet, not a country, but you get the point. And who knows, there might be a Betazed country on the planet Betazed. I don't know. You can also use the IsNull function in your VBA code. For example, here, we're going to say if IsNull(Address), then we're going to change the background color of the address field to red. Otherwise, we'll set the background color to white. If you've never programmed in VBA before, go watch my VBA video. Of course, we have to run this code somewhere, so we'll make a Private Sub for it called CheckAddress. Then we'll call that subroutine from the AfterUpdate event of the address field so it changes when we change the address field. We'll also put the same thing in the form's OnCurrent event so it runs when we move from record to record. This is what it'll look like. If there is a value in there, you'll get the white background. If it's null, you'll get the red background. Here's the form in action. If I come in here and delete this address and press tab, the AfterUpdate event runs, evaluates the address field, sees if it's null or not, and changes the background color. If I move to a different record, the OnCurrent event runs and does the same thing. If I come back over here and type something in, it goes back to white. All that cool stuff is covered in my developer classes. Again, links are down below. Be careful with your use of the "not" keyword. If you're using query criteria, it's "is null" and "is not null" - separate words. Whereas if you're using the function, it's IsNull (one word), and then your field goes in parentheses after it. Then "not IsNull" - it's a little different depending on your usage. Yes, I know it's confusing, but you get used to it. Don't complain to me. I didn't make this stuff up. I didn't build it. My job is just to teach it to you. If you want to learn more about all the different stuff I talked about in today's class, I have lots of different videos. You'll find links to all this stuff in the text description down below the video window. There's query criteria, calculated field, the iif function, and intro to VBA. If you've never done any VBA programming before, it's not difficult. Don't let it scare you. It's easy. You learn a couple of commands and it'll make your database that much more powerful. Access Beginner Five is where I cover AND versus OR for your query criteria. Of course, there's a link to my developer classes if you want to learn more cool programming and stuff. So that's it. There's a Fast Tip. That's null. That's how null works. I hope you learned something. I hope you enjoyed. If you want to learn lots more, I have my whole TechHelp series of videos too. Silver members and up get access to all my extended cut videos, which are really, really cool. Thanks for watching. See you next time. How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you finish the beginner series. Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full-length courses found on my website, not just for Access, too. I also teach Word, Excel, Visual Basic, and lots more. You can now become a Diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you're a sponsor. You'll get a shoutout in the video and a link to your website or product in the text below the video and on my website. But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free. QuizQ1. What does it mean for a value to be 'null' in Microsoft Access?A. The field has a value of zero B. The field contains a space character C. The field contains no valid data; it is missing or unknown D. The field has a default string Q2. How can you find records with null values in a query in Access? A. Use the criteria '= ""' B. Use the criteria 'is null' C. Use the criteria '< 0' D. Use the criteria 'null = true' Q3. What is the correct syntax to find records that have data (not null) in Access query criteria? A. is missing B. not equal null C. is not null D. contains data Q4. The IsNull function in Access: A. Checks if a value is less than zero B. Checks if a value is a number C. Checks if a field is empty or unknown (null) D. Checks if a field contains text Q5. What does the IsNull function return? A. The value of the field B. The field name in uppercase C. True or false D. A numeric value Q6. Where can the IsNull function be used in Access? A. Only in queries B. Only in VBA code C. In queries, forms, and VBA code D. Only in table design Q7. Which function allows you to conditionally display a value like "USA" when a country field is null? A. Nz B. IIf combined with IsNull C. SUM D. Count Q8. If you want to change the background color of a form field in Access based on whether it's null using VBA, which event should you use to check every time a record changes? A. OnClick B. BeforeDelete C. OnCurrent D. OnOpen Q9. To check for nulls in a calculated field that replaces null with "USA", which formula is correct? A. Country2: IfNull(Country, 'USA') B. Country2: IIf(IsNull(Country), 'USA', Country) C. Country2: NZ(Country, 'USA') D. Country2: ReplaceNull(Country, 'USA') Q10. In query criteria, how should you use the "not" keyword with nulls? A. Use 'notnull' B. Use 'is not null' C. Use 'IsNotNull' D. Use '!= null' Q11. What is a key difference between using "is not null" in a query and the IsNull() function in VBA? A. "is not null" is used in forms, IsNull() in queries B. "is not null" is not valid in Access C. "is not null" is used in query criteria, IsNull() is a function for code/expressions D. They are interchangeable Q12. Which Access membership level gives you access to all full beginner courses for every subject as well as developer classes? A. Silver B. Bronze C. Platinum D. Gold Q13. What does the Access Beginner Level 5 class specifically cover, as mentioned in the video? A. How to write VBA code B. How to import data from Excel C. How to use AND vs OR in query criteria D. How to create relationships between tables Answers: 1-C; 2-B; 3-C; 4-C; 5-C; 6-C; 7-B; 8-C; 9-B; 10-B; 11-C; 12-C; 13-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. SummaryToday's video from Access Learning Zone focuses on explaining what null means in Microsoft Access and how to work with null values in your databases.In Access, a field is considered null if it contains no valid data. This typically indicates missing or unknown information. For example, if some customers have empty address or city fields, those fields are null. If you do not set a default value for a field and also leave it blank when entering data, Access records that as null. You can easily find records with null values by building a query and using "is null" as the criteria for the relevant field. This technique helps locate which records are missing information. If you apply "is null" to both address and city fields in your query, only customers with both fields empty will appear in your results. If you want the opposite - to see records where customers have filled out their address and city - use "is not null" in your query criteria. The same method works if you also want to check additional fields like state or zip code. This way, you only return records with complete information. Besides using "is null" and "is not null" in your queries, Access also provides the IsNull function. This function can check whether a specific field is null and returns true or false accordingly. IsNull is very useful in various places, including queries, forms, and VBA code. You can also combine IsNull with the Immediate If function (iif) for more flexibility. For example, perhaps you want your database to display "USA" whenever a country's value is blank, since you may not enter a country for U.S.-based customers. By creating a calculated field that uses the iif function with IsNull, you can automatically insert "USA" wherever the country is null and otherwise keep the entered country. The IsNull function is also handy in VBA programming. For instance, you might write code that checks whether the address field is null and then changes the background color of that field in a form, turning it red if it's null, or white if it has data. You can set up this logic to run when the field is updated or when you change records in the form. While working with these features, keep in mind the distinction between the different ways of expressing conditions. In query criteria, use "is null" or "is not null" as two separate words. When using the IsNull function, you use it inside parentheses with your field name. If you want the opposite result, you can use "not IsNull" as needed. These differences are important for making sure your queries and code work properly. If you are interested in learning more about query criteria, calculated fields, the iif function, or an introduction to VBA, I have several videos and lessons dedicated to these topics. You will find all the relevant links on my website for further study. VBA programming in Access is not complicated, and anyone can learn the basics. With just a few commands, you can make your database more powerful and responsive to your needs. If you want more details, my Access Beginner courses cover topics like AND versus OR for query criteria, and my developer classes introduce more advanced programming techniques. To sum up, this Fast Tips session has introduced you to handling null values in Microsoft Access, finding null fields, creating calculated query fields to substitute values for nulls, and responding to null fields with formatting in forms using VBA. If you want more details and step-by-step guidance on everything discussed here, you can find a complete video tutorial on my website at the link below. Live long and prosper, my friends. Topic ListWhat null means in Microsoft AccessIdentifying null values in tables Finding null values using query criteria Using "is null" in query criteria Using "is not null" to find records with data Using the IsNull function in queries Using the IsNull function in forms Using the IsNull function in VBA code Creating calculated fields to replace nulls with default values Using IIf with IsNull to display alternate values Conditional formatting based on null values in VBA Calling subroutines from AfterUpdate and OnCurrent events Distinguishing between "is null"/"is not null" and IsNull in code |
||||||||||||||||
|
| |||
| Keywords: FastTips Access null, isnull, is null, not isnull, is not null, ms access what is null, IsNull Function, data is missing or unknown, criteria, vba functions PermaLink What is Null in Microsoft Access? |
