Quick Queries #60
By Richard Rost
6 months ago
The #1 Mistake People Make with Null in MS Access
In this Microsoft Access tutorial, I will show you how to handle Null values properly in your databases, explain why Null is not equal to anything (not even itself), and cover the right ways to use IS NULL, IS NOT NULL, ISNULL, and the NZ function. I also answer student questions about working with Nulls in queries and discuss features that users wish Microsoft would improve in Access. Null Ain't Null!
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Null, IS NULL, IS NOT NULL, ISNULL function, NZ function, zero-length string, conditional formatting, tab control, subform control, compact and repair, Word automation, Access runtime, Gantt chart, custom counter, regular expressions, source control, Monaco editor, SQL Server integration, Copilot, web-based Access
Intro In this video, we'll talk about the biggest mistake people make with Null values in Microsoft Access and explain why Null is not equal to anything, not even itself. We'll discuss how to correctly handle Nulls using the IS NULL and IS NOT NULL operators, the ISNULL function, and the NZ function for substituting values. You'll also hear answers to viewer questions about handling Nulls, building conditional expressions, and other common Access concerns, as well as comments about features users wish Microsoft would improve.Transcript Today we are going to talk about the number one mistake people make with Null and their Microsoft Access databases. Hint: Null ain't Null.
Null is not equal to anything, even itself. Plus, we have answers to your YouTube comments, questions, stuff I get emailed, and lots more. Queries number 60. Happy Friday. Here we go.
First up today we have a question from Tim, who posted this in the forums on my website. I am actually thrilled that I finally got to help someone in my own forums today. Usually, by the time I see a question in the forums, the moderators or some of the other students have already answered it. But today I happened to catch this one right after it was posted, so I was able to help.
That is one of the reasons I do not help out on other websites. I used to help out a lot on different websites and Reddit threads, but by the time I see questions now, they are already answered, so I do not get a chance. That is why most of the stuff you see here in the quick queries is stuff that was either emailed to me or was a YouTube comment. Usually, when students email me something, I say post it in the forums because you have a better chance of getting a faster answer there. I get way too many emails. But sometimes if it is a good question, I will throw it into a quick queries video.
On YouTube, I am the only one who does the moderation there. I think I set Alex up as a moderator on YouTube years ago, but he never seems to do anything there.
Anyways, Tim posted a question. He is trying to say basically, if the company name is null, then I want to use the first name. Otherwise, if there is a company name, use the company name. I see this mistake a lot, especially from beginners, because null is one of those tricky concepts. It does not behave like zero or an empty string or even a blank field. Null means unknown, and by definition, an unknown can never equal anything, not even another unknown.
That is why if you ask Access, does null equal null, the answer is false. So to handle null, you need the right tools. There are special operators and a special function designed just to deal with nulls.
Null does not mean zero. It does not mean empty string. It does not mean anything except null. It is unknown or no value, and no value can ever equal null, not even another null. So null equals null is false. If you say does null equal null in Access, it will say false.
I was going to say the word "nothing" there. Nothing equals null, but that is a whole separate term. There is actually a term in Access called "nothing" used for object variables. That is a whole different video.
In Access, you have two different ways to check for null. There is the IS NULL function, and there is the IS NULL operator or IS NOT NULL. You never say equals null.
So IS NULL and IS NOT NULL. You will see this used in queries or SQL statements. It is an operator. You would say, for example, if you know SQL, "select all the fields from customer table where company name is null" or "company name is not null."
If you are a beginner and you are not writing SQL statements yet, in your query criteria for your field, you put IS NULL or IS NOT NULL right there in the criteria field.
Now, ISNULL with no space, ISNULL is a function that returns a value of true or false. It is used in expressions and in VBA. For example, in a query, you might say make me a new calculated field called DisplayName, use the IF function (immediate if), and you are going to say "if isnull(company)". This will return a true or false value.
Then IF works by saying if this is true, use the first value, otherwise use the second value. The company name is null, then use the first name, otherwise use company name. It is the same thing you will see in VBA written out this way. These are two exactly identical statements. If isnull(company), then DisplayName equals first name, otherwise DisplayName equals company name.
Again, here is a simpler version for beginners: DisplayName, and use the IF function: isnull(company name), first name, and so on.
We will never say if first name equals null. Nothing equals null, not even null. You should never put it inside an IF function as "if first name equals null"; that is bad too. That is why these are in red. Red means bad.
So Tim, to answer your question, all you want to do is replace this stuff here with "if isnull(company name)", not "company name equals null".
A few other things: you do not need to specify "Full Name, q." in all of these places if that is the only thing in your query. If this is based on just that one query, if you have other tables or queries joined in, then yes, leave those there.
Since you are good and you have not used spaces in your field names, you technically do not need those brackets too, although Access usually throws those in for you. This is the simplest version of what you need right there, meaning you only have one table or query in your query.
A little bit of an advanced trick here: if you want to make it even easier or even shorter, you can use the NZ function. NZ stands for "null to zero," which is usually used for numeric fields. You are basically saying if this value is null, make it zero, but you could specify whatever the "make it" value is here. So this basically says if company name is null, then use full name. It is a shorter way of writing that out, and you will see this a lot with lookups, a little more advanced.
In review: you almost never check to see if something equals null with the equal sign because nothing equals null, not even null itself. That is why Access gives us the IS NULL operator, IS NOT NULL, or the ISNULL function. If you want a handy way to remember it: Null ain't Null. In other words, Null will never equal Null, so do not try to see if anything else is equal to Null. Maybe this should go on a t-shirt or a mouse pad or a coffee mug and be put in the store.
I have a whole bunch of other videos on my website dealing with null, and I work with it a lot in my full course. Here is a fast tip video explaining the basics of null. Start here, watch this.
Watch this video on zero-length strings. A lot of people get null values confused with zero-length strings. Here is a video on "null math." Yes, you can actually rely on values being null, and put that together with some concatenation and you get some cool results. So check this one out. Here is a video on that NZ function.
These are all free videos. They are on my website. They are on my YouTube channel. Go check them out. I will put links down below.
Speaking of my YouTube channel, let us head over to the comments. I did a little conversation starter. I am going to do these once in a while. I love doing these. I love getting your feedback and learning what you think about stuff.
This week's conversation starter I posted: What is the one feature in Access you wish Microsoft would improve and update?
I got a ton of replies. I am going to go over a couple of them.
Jim says: How about additional options to conditional formatting? Yes, absolutely. Microsoft Excel has some fantastic conditional formatting options. Access, the redheaded stepchild, needs a little bit of love there. Great idea, Jim.
Dom says you would like to see a better implementation of the tab control. Yes, the tab control is one of those things where, when it was first released, it was buggy as hell. I did not like it. I still do not like it. I still do not use it. I do not like the tab control. It will work with simply, but try integrating it with some VBA and it becomes a nightmare. They fixed a lot of the problems that I initially had with it, but once you get a bad taste for something, you just do not go back to it. I do not like the tab controls.
He says, would you dedicate a video to the subform control? I have a lot of videos on subforms. The one you are looking for, where I used a subform control to simulate a tabbed page, was in my "Association Database" series, part eight. You are looking for this thing right here, where I click on this, these are basically labels, you click on the label and it switches what is in the subform. It changes the subform object property, which I think is much, much better than using a tab control.
I also show something similar in part one of my ABCD, where we have little tabs to switch between the different things for each customer. Same thing.
Excel and Delegente says you would like to see Access automatically repairing and fixing the file and closing. You can do a compact and repair on close. I do not recommend it. I think you should do compact and repair like weekly, depending on the usage of your database, but I do not like doing a compact and repair on close. You should compact your backend databases maybe once a week with an event or do it manually.
Preventing it from growing unnecessarily in size, that is exactly what compact and repair is supposed to do. I have mine run every Sunday at like 4 a.m. and every week it squeezes two, three, sometimes five percent out of it. It is just getting rid of all those things that were deleted, temporary data files, that kind of stuff. Do not load it up with images and file attachments and stuff that does not belong in there.
As far as more form properties and professional design like Visual Studio, yes, absolutely.
Kevin says adjustable font size in a property sheet and controls. Yes. When you make it bigger, it should make the fonts bigger, the controls bigger. There is supposed to be adding zoom very soon. It has been on the Access roadmap for a while now, for a couple of months now. It was supposed to be out in September. Now it has been pushed back to October. We are hoping, fingers crossed, next month. I am looking forward to it myself because I have a pretty big monitor. I like to be able to zoom in on stuff, and large monitor support is supposed to be coming too. I am excited for that.
Gracie says, I like the photo. Good looking, good. That is actually a picture of me that I kind of had AI make look like I am doing that, like looking up to the side. That is not really a real photo. That is my face, but it is a little photo trickery. I know what you are saying there as far as the appearance should not be that of Access. I know a lot of people want to develop databases and make it not look like it is Access.
In fact, I did a two-part series on how to make your database not look like it is Access. To the average everyday user, you can get away with it, but to someone who really knows what they are doing, they will be able to tell it is Access. But you can pass it off to most people as not being Access.
Hussein says connecting with AI. I would like to see them add some AI features built into Access, like Copilot, which they have in Word and Excel and all that, which again, redheaded stepchild. Check out my OpenAI videos. If Microsoft is not going to build it into Access, we will have to build it in ourselves. Check this out.
Jess says exporting to docx, which is a Word document instead of RTF. That would be nice if you could take an Access report and have it export directly to a Word document that is actually fully editable.You could do a PDF if all you want to do is share it or print it or whatever, but yeah, that would be nice. You can do a lot of things with Word automation. In fact, I have a video here that teaches you a little bit of Word automation to take some data out of your Access database and create a Word document, and add that stuff to the Word document using VBA.
With that technique, you can do a lot of stuff. Half of my handbooks are built using some Word automation. It adds the picture and formats all the fonts and the headings and all that stuff. So you can do a lot of things. I have not covered a ton of it in my lessons, but I am going to be soon, eventually. We will get there.
But yeah, it can be done, but it would be nice if it was built into Access automatically.
Greg's Drag-in says, allow developers to make an executable out of their database front ends. Yeah, that would be super cool. Years ago, I used to use InstallShield to take an Access database and it would package everything up that you needed, including all the executables, the runtimes, and all that, to make a distributable version of your Access database.
I have not played with anything like this in recent years, so I am not exactly sure myself what is out there. I have not had a need to do this.
Microsoft does have the Access runtime, which means if you want to give your database to someone else to use who does not have Access, all they have to do is install the free runtime edition. But it is still not the same as giving them a file that installs everything for them with your database. I see what you are going for there, but I do not know what is out there right now myself. I have not researched it in a couple of years.
If anyone else has any good products that they used to do this, let me know.
It says a proper Gantt chart. Yeah, that would be nice. I built one myself a couple of years ago, built a template out of it here. It is not as flexible as what you can do with some other software, but if you really need a Gantt chart in Access, this will do it. It is not as feature packed as even Excel is, but it gets the job done. Mostly this was a project that I wanted to build to see if it could be done. But it is a good stepping stone if you want to take it and customize it for what you need.
Andrew says the ability to resort ID numbers, to which I am going to remind you that auto numbers are not for your use. They are for Access to make relationships and to make sure that records have a unique identifier. If you want to do something like ABC1, ABC2, you do need to create your own custom counter.
I have videos to show you how to do it, but you are right. You need a little coding to do that. It is not easy to do. It would be nice if they had some kind of a beginner-level counter field, for example, that just automatically did it.
Here is this video. This talks about what auto numbers are used for and what they should not be used for. And here is a video that teaches you how to set up your own counters.
As Lahn says, add regular expressions to the SQL engine, no custom VBA function. They did make an update recently when it comes to Regex. The guys over at Access Forever just posted, Philip just posted an article because VBScript is going away. It is built into Windows and that is what handles regular expressions. But they recently just added it as a library in VBA. So you will still be able to continue to use it.
I have not played with it yet myself to be honest with you, but check this article out for more information. I will post a link to it down below and thanks always to the guys at Access Forever. If you are not subscribed to these guys, do it. They do not post a lot of stuff, but they post a lot of good stuff. I get a lot of my news from them and from Mike Wolf over at No Longer Set.
Easy Speaks says definitely the developer section of its offline help file. Yeah, that is pretty old. That could use some updating. That is a lot of work though. Speaking as someone who writes handbooks or who used to write handbooks, I have AI do it and my human editors fix it up for me now, but I used to write them myself. It is a lot of work updating help files. But yeah, it should probably be done. But then again, if they had really good help files, you guys would not need me.
Fuego says yes, ability to zoom into the work area. I am getting old. Yeah, we talked about this earlier. Excel and Word can zoom in and out. Why cannot Access? So it is supposed to be coming. Fingers crossed.
Got a lot of feedback on the Quick Queries 59 last week about why IT pros hate Access. A few people said that they do not like it because it is not web-based, which is fair. Access is not nor has it ever been marketed to be a web-based database. It is desktop only. But they used to have the Access data pages, the web apps that you could build that they got rid of, I think with Access 2016. They axed those. They never really worked right, but yes, it is just how it is.
I have been working on something myself, but it is still far from releasable. That would be nice if Microsoft built something like that. It would not be hard because they already have a path to migrate your data up to SQL Server. Now you just need to have the ability to take an Access form and turn that into a web page. That is kind of what I am working on, but it is slow going. I got it working in its simplest form, but adding VBA behind the scenes is the tough part.
A few people have said that their IT departments hate Access and refuse to help with Access questions. That is probably because they do not know Access. More than a couple of people have told me that as IT professionals, they hate having to help people with their problems with Excel or Word. I used to get this a lot too. Everyone thinks that because you are a computer guy, you know everything there is to know about everything on computers.
This might have been true 50 years ago, when it was possible to kind of know everything about PCs. Well, not 50, maybe 40 years ago. But now it is like, people ask me questions about something I have never even used before. Now when people ask me, I am very specific. I say I make video tutorials to teach Microsoft Access mostly. Then they move on to the next topic.
Never have I run into someone that was, oh really? I work with Access every day. Never. That is why I do not mention Excel at all, because everybody uses Excel.
Scottie says in finance and accounting and all the jobs he has had, no one wants to deal with learning Access, so he is forced to write everything in Excel Power Query. Yeah, that is definitely true. Excel is a lot easier to get started with and to feel like you are making progress right out of the box because you can pretty much type anything anywhere you want. You do not have to learn structure or build queries.
Access is one of those things where if you take the time to learn it up front, it can be a lot more powerful. But people do not take the time. Send them to me.
Mohammed says people hate it because after creating your software in Access, you still need a license key to use it. I am not sure what you mean by that. If you build the database and you want to give it to someone else, they have to buy Access. See my earlier comment that I made about the runtime. They can just install the runtime and use your database for free.
The only person who needs a paid copy of Access is the person who is developing. That is you.
Off topic, but fingerprint system, Access does not support fingerprint readers natively, but different fingerprint readers sometimes come with their own SDKs (software development kits). I have yet to play with one. I do have fingerprint readers on all of my laptops and they are all the same model. I have yet to look into seeing if I could figure out how to get the fingerprint reader to work for logging into Access.
I got it working, logging into my websites, not my website but the different websites that I use, and logging into Windows itself with Windows Hello. If enough of you want to see how to get Access to work with fingerprint readers, let me know. Maybe I will do a little research. If more than a couple of people are interested, maybe I will look into it.
Here is a story I see quite a lot. Mark, who is now retired, designed Access programs for several companies, and then they hired an IT guy or IT firm to create a more professional version that did not work properly. After a year, the failure became even worse. So now the client is back working with the Access program and realizes they are very satisfied with it.
I hear this a lot, not just from you. I hear this story all the time. Oh, we hired this consulting firm, they said they can make us web-based and enterprise level, and about $30,000 later, it is still not as good as our Access database was. Common story.
The last two companies I worked for do not even allow it to be installed on machines without explicit permission. I have seen that. I get why some companies do that. If it is something that the company is not going to support, they do not want you building something that becomes crucial for your job. Then you leave that job or they get rid of you, and then nobody else understands how your Access database works. So I kind of get that. They want their employees to stick with Word or Excel or whatever because the next person can come along and more easily figure it out. But that is not a good excuse. It is just an excuse.
Allin Tosh says if VBA could natively support Git, then I think it would get more buy-in from IT professionals. Source control in Access database kind of sucks. Yeah, it is pretty much non-existent without an add-in. From what I have heard and what I have read, they are going to be adding some kind of source control to Access. It is still not there yet, but I am going to be doing a little more on this myself in the near future.
Alex, my right-hand man, has been wearing on me for years to get involved with source control in Access, and I have been fighting it because I have always been a one-man developer. My source control is I make a backup copy before I make changes. If the changes are good, I am good. If not, I go back to the backup and figure out what I did wrong. So that has always been my source control.
I have always felt that source control works best in teams. But I get it. I understand. It is nice to go back and see what the changes were and what you might have broken, all that stuff. I am slowly wearing down to agree that working with source control might be good with Access.
What do you think? Do you use source control in other projects that you work with?
Deribelos says I love Access, been using it for 10 years. He hates the SQL text editor. He is sure that Microsoft is sabotaging it, to force users to adopt SQL Server. I would not say that. SQL Server has been around for a very, very long time. Microsoft has always held that for bigger databases and for corporate security, you should use SQL Server for your data, for your tables and your queries and stuff. That has never changed. I do not think that Microsoft is sabotaging the SQL editor.Now, Access, the classic version of Access, has a plain SQL editor, which I still use - the older version. They are working on a new Monaco editor, which has been out for a little while now, but it has some bugs. So, I am holding off on adopting it.
I like to wait until they have finished something and it's bug free before I start teaching it and making videos about it. But it looks snazzy, and once they fix some of the issues they have, I will definitely be doing some videos on it.
But they are not Microsoft that is sabotaging it. Now, Microsoft does not give Access the love it deserves. Most Access developers will say that the amount of resources that Microsoft spends on Access could be tripled easily, because it is a fantastic program that thousands of businesses use to run their businesses.
But, yes, that means Microsoft has been saying that the upgrade path for your data is SQL Server for decades. And I agree. When you are running a business with sensitive data, you really should have it in SQL Server.
Microsoft says it's not SaaS - software as a service - which everyone is now pushing for. Unfortunately, unlike Word, Excel, and Outlook, you cannot run Access from Microsoft 365. Access is still desktop only, whereas Word, Excel, and Outlook have web-based versions now, which I do not like. I do not use them unless I am in a pinch and I am somewhere else without my computer with me. Even then, I still do not like them. I prefer the desktop applications.
But it would be nice if they had Access as part of the online 365 suite. That would be sweet. But it is not there yet, but I agree. Having an online version of Access that runs just like the desktop version and connects to SQL Server, that would be the killer app for Microsoft to release right there.
Well, folks, that is about going to do it for this week's Quick Queries. I hope you learned something. Live long and prosper, my friends. Enjoy your weekend. I will see you next time.
TOPICS:
Understanding how Null works in Microsoft Access
Why Null is not equal to anything, not even itself
Checking for Null using IS NULL and IS NOT NULL operators
Using ISNULL function to handle Null values in queries
Avoiding the use of "= Null" in expressions
Creating expressions to display a default value if Null
Using NZ function to substitute values for Null
Differences between Null, zero, and empty strings in Access
Common beginner mistakes when handling Nulls in Access
Building conditional expressions for field display based on Null
SQL criteria for fields that may be Null in Access queries
COMMERCIAL:
In today's video, we're going to talk about the biggest mistake people make with Null values in Microsoft Access and why Null does not equal anything, not even itself. We'll discuss the correct way to handle Nulls using the IS NULL operator, the ISNULL function, and the NZ function for making your queries work right. Plus, we'll answer questions from viewers about improving Access, common frustrations, and share tips for working with forms, tab controls, compact and repair, automation, and more. You will also hear what features users wish Microsoft would add to Access. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the primary mistake people make when dealing with Null values in Microsoft Access?
A. Treating Null as zero
B. Treating Null as an empty string
C. Using '=' to compare values to Null
D. Using IS NULL to check for Null values
Q2. Which statement about Null is correct in Microsoft Access?
A. Null is equal to zero
B. Null is equal to an empty string
C. Null is not equal to anything, not even itself
D. Null is the same as 'Nothing' in Access
Q3. When checking for Null values in Access queries, which operator should you use?
A. = NULL
B. IS NULL
C. == NULL
D. EQUALS NULL
Q4. What does the ISNULL function do in Access?
A. Checks if a value is empty
B. Checks if a value is zero
C. Returns True if a value is Null, False otherwise
D. Replaces all Nulls with zeros
Q5. Which field type is typically associated with the NZ function in Access?
A. Text
B. Numeric
C. Date/Time
D. Attachment
Q6. What is an advantage of using the NZ function with Nulls in queries?
A. It makes fields read-only
B. It converts all values to text automatically
C. It allows specifying a replacement value if Null is found
D. It deletes Null values from the database
Q7. What is NOT a correct way to handle Null in Access expressions?
A. Use IS NULL in query criteria
B. Use ISNULL in an expression or VBA
C. Use = NULL in a conditional statement
D. Use NZ to substitute for Null
Q8. Why should you avoid using "= Null" in Access expressions?
A. It only works in VBA
B. It sometimes returns True
C. Nothing equals Null, not even another Null
D. It only works for numbers
Q9. Which of the following is true about zero, empty strings, and Null?
A. Null, zero, and empty string all behave the same
B. Zero and empty string are considered Null
C. Null means unknown and is distinct from zero or empty string
D. Null can be replaced automatically by zero or empty string in Access
Q10. What is the purpose of Compact and Repair in Access databases?
A. To reduce file size and optimize performance
B. To delete all Null values
C. To export data to Excel
D. To change field data types
Q11. Which Access feature is described as "useful, but more buggy when integrated with VBA" according to the video?
A. Tab control
B. Combo box
C. Ribbon customization
D. AutoNumber field
Q12. What is the Access Runtime?
A. A paid version of Access for developers
B. A free version for end users to run databases without full Access
C. A licensing tool for developers
D. An online version of Access
Q13. What is a common issue with using AutoNumber fields as IDs for user-facing purposes?
A. They are not guaranteed to be unique
B. They can be easily edited by users
C. AutoNumbers are for relationships, not user-facing IDs
D. They increase database size too much
Q14. According to the speaker, why do many IT professionals dislike supporting Access?
A. They do not know Access well
B. Access is web-based
C. Access is too expensive
D. Access always corrupts data
Q15. Which of the following is NOT discussed as a desired improvement for Microsoft Access by viewers?
A. More conditional formatting options
B. Better tab controls
C. Built-in AI features
D. More advanced mail merge for Outlook
Q16. Why is source control generally important for database development?
A. It guarantees faster databases
B. It allows tracking changes and reverting to previous versions
C. It only works for web databases
D. It makes backups unnecessary
Q17. What is the main advice given regarding upgrading Access data for enterprise-level security?
A. Export all data to Excel
B. Store critical data in SQL Server
C. Only use Access for web-based applications
D. Use only local desktop storage
Q18. Which of the following statements about Access and the Microsoft 365 suite is true?
A. Access is fully web-based in Microsoft 365
B. Access can be run in any browser
C. Access is desktop-only, not available as a web app in 365
D. Access has no desktop version
Q19. What is Microsoft's recommended "upgrade path" for data when outgrowing Access?
A. Export to PDF
B. Migrate to SQL Server
C. Move data to PowerPoint
D. Use SharePoint lists
Q20. When using VBA in Access, what is "Nothing" primarily used for?
A. Indicating a Null value
B. Used for undeclared variables
C. For object variables with no reference
D. For numeric fields set to zero
Answers: 1-C; 2-C; 3-B; 4-C; 5-B; 6-C; 7-C; 8-C; 9-C; 10-A; 11-A; 12-B; 13-C; 14-A; 15-D; 16-B; 17-B; 18-C; 19-B; 20-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 In today's Quick Queries video from the Access Learning Zone, I want to talk about the single biggest mistake people make when dealing with Null values in their Microsoft Access databases. I'll give you a hint: Null is not actually equal to Null.
Let's get right to the point. In Microsoft Access, Null isn't equal to anything, even itself. This trips up a lot of beginners and even some experienced users from time to time. Today, I'll go over that foundational concept and also respond to a variety of questions and comments I've received from students through YouTube and email.
The first discussion for today comes from a question posted in my website forums. I don't always get to answer questions there myself since the moderators or other students are usually pretty quick to respond, but this time I managed to catch one just after it was posted.
This leads to an important point: while I used to help answer questions on all sorts of other forums and sites, I don't do that much anymore. By the time I see questions, others have usually taken care of them already. That's why most of what you see in these Quick Queries videos comes straight from emails or YouTube comments. I get far too many emails to answer each one personally, so I encourage students to use the forums for faster responses. Still, if a question stands out as particularly interesting or useful, I might include it here.
So, Tim posted a question about handling situations where a company name might be Null. He wants to use the first name if there's no company name present but use the company name when it exists. This situation is really common, and it highlights a critical misconception with Nulls in Access.
Null is a unique value. It doesn't mean zero, an empty string, or just a blank field. It specifically means unknown. By definition, something unknown can't be compared directly to anything else—not even to another Null. That's why, when you ask Access if Null equals Null, the answer is always false.
Because of that, you can't just use the equals sign to compare values to Null. Access gives us specific tools for this: special operators and functions designed for Nulls.
In Access, there are two methods for checking for Null. You can use the IS NULL operator or the ISNULL function. IS NULL is typically found in query criteria or SQL statements, like "select * from Customers where CompanyName is null." It is an operator and not a function, and if you're not working directly in SQL, you'd use IS NULL or IS NOT NULL in the criteria field of your query.
Now, ISNULL (spelled as one word with no space) is a function that returns a True or False value. You use it in expressions and VBA. For example, you could create a calculated field in a query, and use the IIF function: "if IsNull(company), then use FirstName, otherwise use CompanyName." The same approach works in VBA. Just remember, you should never use "equals Null" in your expressions or code. That's never correct.
For instance, you shouldn't write "if FirstName equals Null" inside an IF function or WHERE clause. That logic will never be true.
Tim, to answer your question, all you need to do is replace "CompanyName equals Null" with "IsNull(CompanyName)". That's it—no equals sign to Null, ever.
One more detail: if you're working from just one table or query, you don't have to specify the field name with the table or query alias every time. Also, since you don't have spaces in your field names, those brackets Access adds are technically not necessary.
Here's an advanced shortcut: you can use the NZ function. NZ stands for "null to zero," but it's flexible; you can specify the replacement value, so if CompanyName is Null, use FirstName. This is a compact way to write your logic, and you'll see it used a lot in more advanced applications.
To summarize: Never check for equality to Null with an equals sign. Use IS NULL, IS NOT NULL, or the ISNULL function. Keep in mind: Null isn't Null. It will never equal itself or anything else. Always use the correct tools when handling Null values.
I have several other videos on my site that deal with Nulls more deeply, including videos about zero-length strings, Null values in calculations, and the NZ function. These are all free resources available both on my website and my YouTube channel. I'll include links below.
Switching gears, I posted a conversation starter this week: What is one feature in Access you wish Microsoft would improve or update? I got some great feedback from all of you.
Jim mentioned enhancing conditional formatting options in Access. I couldn't agree more. Excel has far better tools for this, and Access absolutely deserves more attention here.
Dom talked about improving the tab control and asked for content on subforms as a replacement. I've covered this in my "Association Database" series, specifically part eight, where I simulate tabbed pages by switching subforms with labels, making for a much simpler and more flexible setup than using tab controls. A similar method is used in the ABCD series, part one.
Another topic raised was automating compact and repair operations. While it's possible to have Access compact and repair on close, I don't recommend doing it this way. I suggest compacting your backend databases manually or with a scheduled event, such as weekly. This helps prevent your databases from becoming bloated over time.
Kevin asked about adjustable font sizes in property sheets and controls. There's supposed to be a zoom feature on the way, which I'm really looking forward to myself.
There were also comments about making Access look less like Access for end users. I have a video series on making a database appear more custom and less like a standard Access file.
Several of you also mentioned wanting AI integration, exporting reports to Word (docx) instead of RTF, creating executables from Access database front-ends, and better Gantt chart support. On that last point, I've built a template for creating a basic Gantt chart in Access. It's not as robust as dedicated project management tools, but it gets the job done for most internal uses.
Andrew brought up the idea of being able to re-sort ID numbers. Remember: AutoNumbers in Access are for internal use—relationships and unique identifiers. If you need custom numbering formats, you'll have to code your own solution. I have videos covering that as well.
There was also discussion of regular expressions in Access. Previously, using Regex required VBScript, but recently Regex support was added as a library in VBA. If you're curious, check the Access Forever article for the latest details.
Several people mentioned that the developer help files could use a major update, and I agree.
Zooming in the Access work area came up again. It remains an outstanding feature request that I hope to see soon.
I also got a lot of feedback about why IT professionals tend not to like Access. Many say it's because Access is not web-based. That's true—Access is for desktop applications only, and the web app features disappeared years ago. It would be a game changer if Microsoft allowed Access forms to migrate to web pages, combined with SQL Server on the backend.
Another common issue is that many companies and IT departments don't want to support Access, fearing the risk of business-critical databases being left orphaned when employees leave.
Someone else asked about native Git support in Access for source control. This isn't present yet, but there are rumors it will be added in the future. Source control is especially useful for team environments.
Regarding the SQL text editor, some of you noticed Microsoft is pushing updates and working on new features, though there are still bugs. Microsoft does encourage those who need advanced data solutions to move toward SQL Server.
And the topic of running Access as a web or cloud-based solution—right now, you can't run it as part of Microsoft 365 in the browser like you can with Word or Excel. Personally, I prefer the desktop tools, but I know many of you want an online version. That would be a huge advance.
To wrap up: Null in Access is never equal to anything, including itself. Always handle it with the special operators and functions provided. Thanks for all your great questions and comments. I really appreciate the interaction and the ideas you send in each week.
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 Understanding how Null works in Microsoft Access
Why Null is not equal to anything, not even itself
Checking for Null using IS NULL and IS NOT NULL operators
Using ISNULL function to handle Null values in queries
Avoiding the use of "= Null" in expressions
Creating expressions to display a default value if Null
Using NZ function to substitute values for Null
Differences between Null, zero, and empty strings in Access
Common beginner mistakes when handling Nulls in Access
Building conditional expressions for field display based on Null
SQL criteria for fields that may be Null in Access queriesArticle Understanding how Null works in Microsoft Access is essential for anyone building databases, especially if you are new to the platform. The number one mistake people make with Null is misunderstanding what it actually means and how it works within Access. Null is not the same as zero, an empty string, or a blank field. Null simply means "unknown" or "no value." This is a subtle but important point, because many beginners try to check if something is Null by comparing it using an equal sign, for example: if field = Null. The problem is, in Access (and in SQL generally), Null is not equal to anything, not even itself. So if you write Null = Null, the answer is false.
To check if a field is Null in Access, you have to use specific tools: either the IS NULL operator or the ISNULL function. The IS NULL operator is used in SQL statements and query criteria. For example, if you want to find all records where the CompanyName field is Null, your SQL would look like this:
SELECT * FROM Customers WHERE CompanyName IS NULL
If you are building the query with the graphical query designer, you just type IS NULL in the criteria row for your field.
On the other hand, the ISNULL function is used inside expressions and VBA code. It returns True if its argument is Null and False otherwise. For example, say you want to create a new field called DisplayName in a query. You want it to show the Company Name if it exists; if not, you want it to show the person's First Name. You would write an expression like this:
DisplayName: IIf(IsNull([CompanyName]), [FirstName], [CompanyName])
This says, if CompanyName is Null, use FirstName; otherwise, use CompanyName. Never use equality with Null, such as [FirstName] = Null, because this will never work. Nothing equals Null, not even Null itself.
A more streamlined way to handle these situations, especially when dealing with text or numeric fields, is to use the NZ function. The NZ function stands for "Null to Zero," but you do not have to convert to zero—you can specify a default value for when the field is Null. For the same example as above, your expression could be:
DisplayName: Nz([CompanyName], [FirstName])
This says, if CompanyName is Null, show FirstName instead. If CompanyName is not Null, it shows CompanyName.
It is important to understand the distinctions between Null, zero, and empty strings. Null means the value is unknown. Zero is a valid number (meaning, specifically, zero), and an empty string is two double quotes with nothing in between (""). People often get confused here, as an empty string is not the same thing as Null in Access.
To sum up: never check for Null with an equal sign. Always use IS NULL, IS NOT NULL, or the IsNull function depending on context. For quick replacements or default values, NZ can be very handy.
Let us quickly mention some VBA code examples so you can see how this plays out in practice. If you are working in VBA and want to check if a variable called companyName is Null, you would write:
If IsNull(companyName) Then
' do something, like use FirstName instead
Else
' use companyName
End If
Or, taking advantage of the NZ function:
DisplayName = Nz(companyName, firstName)
This assigns firstName to DisplayName if companyName is Null.
If you use improper syntax like If companyName = Null Then, your code will not work as intended, because that comparison will never return True. Always use IsNull in VBA and IS NULL in SQL or query criteria.
Beyond Null, there are other frequently requested improvements for Access that come up in user discussions. For example, many users would like to see enhanced conditional formatting (to match what Excel offers), improved tab controls, automatic compact and repair on close, easier zooming in design views, and better support for exporting reports directly to Word documents (.docx instead of .rtf). Some wish for the ability to create distributable executables from their Access databases, more robust Gantt charting capabilities, and beginner-friendly auto-numbering fields.
Another area where users often want improvements is with source control, such as native support for tools like Git. Right now, incorporating version control into your Access development workflow requires third-party tools or manual solutions, but Microsoft has signaled that source control features may be coming down the line.
Lastly, there is ongoing discussion in the Access community about making Access web-based. Currently, Access is strictly a desktop product, unlike Word or Excel, which now have web versions. Microsoft has not provided a web-based Access, and the old web app features were dropped years ago. However, Access is still a powerful tool for many businesses, and if Microsoft ever produced an online edition integrated with SQL Server, that would be a significant advancement.
In conclusion, always remember that Null really means "unknown," and nothing ever equals Null, not even Null itself. Use the right functions and operators to handle it. If you follow these rules, you will avoid a lot of headaches with your Access databases, and your queries and code will behave as expected. |
