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.
For more info please visit:
No comments:
Post a Comment