|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Invalid Use of Null By Richard Rost 
4 years ago
Run-time Error 94: Invalid Use of Null In this video, I'm going to explain the dreaded Visual Basic runtime error 94: invalid use of Null. What it is. How to prevent it. Links
Keywordsaccess 2016, access 2019, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, invalid use of null, null values, runtime error 94, run-time error 94
IntroIn this video, we will talk about the "Invalid Use of Null" error in Microsoft Access, what causes it, and a few different ways you can prevent it from happening in your forms and functions. We will look at how to handle null values in data fields, including using tricks like concatenating empty strings, checking for nulls before calling functions, and using the NZ function to convert null values. If you have run into this error when working with fields like phone numbers or customer IDs, this video will help you understand how to avoid it.TranscriptWelcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, we're going to talk about the error message "Invalid Use of Null," one of my favorite ones, and I'm going to show you how to prevent sending a null value to a function in Microsoft Access. This video came about when one of my subscribers on my YouTube channel, RabbitFollower, pointed out that in my filter characters TechHelp video, if you send the phone number field to the filter characters function, and someone blanked the phone number field, it's going to return "Invalid Use of Null." He suggests concatenating an empty string on the end of that to fix the problem, which it will. There are a couple of different ways to fix this problem, and I'll show you a couple of different ways in just a minute. He also points out correctly that that video is about filtering, and I do not always catch every possibility when I'm putting a video together. That comes in the usability phase later when you're playing with it, and you realize there's a bug, and you fix it then. I was focused mostly on the filtering aspect of it, but yes, we have a problem if someone blanks that phone number field. Let me show you. First, if you haven't watched my filter characters video, go watch that first so you know what I'm talking about. I'll put a link in the description below the video so you can click on that. Basically, it's like if you have someone that types in a phone number with weird characters in it, the filter function will simply remove those and leave only the digits behind. Here's a copy of the database. If I come in here and I put 716-555-3333, hit tab, filter characters filters it out, and it looks like a normal phone number. Now, the problem is, and this is what RabbitFollower pointed out, if I delete that and hit tab, I get "Invalid Use of Null." What does that mean? Well, a null value is basically a value that doesn't exist. You do not have any data there. If you hit debug, you see it brings us to this line here, and the problem is, phone number right now, as you can see, is null. So we have a couple of different ways we can fix this. We can either intercept the value ahead of time and not run filter characters, or we can play some tricks with the value itself. Let's stop this code. The first way we can handle this is by simply saying right here, if isnull(phone) then exit sub. In other words, if it comes in here and the phone number is null, just exit and do nothing. So if I come in here and I do that, and then I delete it, we're all set. But there are also a couple of other tricks we can play. The first trick, and this is what RabbitFollower suggests, is we can simply concatenate an empty string on the end. This will technically convert that whole thing to an empty string. Whenever you concatenate null onto or with anything, it is essentially ignored, and that has the same effect. So if I put something in here, and then delete it, again, no error message. Another way that you could do it, and this is the way I usually do it, is I use the NZ function. It stands for "Null to Zero." Basically, it will convert any null value into anything else that you want. Usually, it's used for zero. I have a whole separate video on NZ, too. You can go watch that one; I'll put a link down below. But if you use NZ(phone), the value, and then what you want to turn that into if phone is null, again, use an empty string. So it will look at phone, convert it to an empty string, and again, it should work just fine. Delete. There we go. You cannot fix it in the function, because you can't send a null value as a function parameter. It would be nice if we could just go into filter characters and deal with it in there, but you can't. So you have to deal with it outside the function. Another time this usually comes up is with parameters like this. If you look at my customer list (save changes, sure), when we double-click on one of these records, I made a double-click event. If you go back and watch the blank database video where I build this, double-clicking opens up Jean-Luc. If you double-click down here, it will throw an error. When I coded this, I took that into consideration in the OnDoubleClick event. Yep. If IsNull(CustomerID) then Exit Sub. If that's not there, you get the same problem. In CustomerList, if you double-click down here, because that's a null value, it just says "new" there, because your new record comes in. Basically, it's the same thing. "Syntax error. Missing operator in expression." You have a null value here as CustomerID, and it doesn't like that. So you can either wrap this in the NZ function, or you can do what I've got here, and just say, if it's null, then don't do it. Get out of town. Thanks once again to RabbitFollower for posting this comment. He has posted a couple of good ones in the past, so Rabbit, I appreciate your feedback all the time. If anyone else has something to say, comments or questions, go ahead and post them. You can also post them on the forums on my website. There is the address right there for the Access forum. I hope you learned something today. Thanks for watching, and we'll 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 higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you've finished 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've finished the expert classes. These are the full length courses found on my website, not just for Access. 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 are a sponsor. You'll get a shout out 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 the error message "Invalid Use of Null" typically indicate in Microsoft Access?A. The field contains a value that is too large B. The field does not contain any data (null value) C. The field contains invalid characters D. The field is locked for editing Q2. What is one way to prevent sending a null value to a function in Access, as shown in the video? A. Use the DoEvents command before calling the function B. Test if the value is null and exit the subroutine if it is C. Multiply the value by zero before sending it D. Change the data type of the field to text Q3. What does concatenating an empty string ("") to a value do when that value is null? A. Sets the value to zero B. Converts the value to the number one C. Converts the value to an empty string D. Generates an error Q4. What is the purpose of the NZ function in Access? A. To square a number B. To check if two values are equal C. To convert null values to a specified value D. To count the number of non-null values Q5. Where must you handle the null value when sending parameters to functions, according to the video? A. Inside the function B. In the table properties C. Before calling the function D. When designing the query Q6. When using the OnDoubleClick event in the customer list, how is a possible null value for CustomerID handled? A. By showing an error message box B. By ignoring it and proceeding with the event C. By exiting the subroutine if CustomerID is null D. By setting CustomerID to zero Q7. What happens if you send a null value as a function parameter in Access VBA? A. The function processes it as normal B. An "Invalid Use of Null" error is thrown C. The function automatically converts it to zero D. It is converted to a Boolean value Q8. What should you check for before attempting to process fields like phone numbers in your code? A. That the field contains at least one digit B. That the field is not null C. That the field is already filtered D. That the field data type is integer Q9. Which of the following is NOT a method described in the video to prevent "Invalid Use of Null" errors? A. Using the NZ function B. Concatenating an empty string C. Ignoring null values in VBA code D. Using a Try...Catch block Q10. Why does the error not appear when you concatenate an empty string to a null value in Access VBA? A. Because the string is always longer than a null B. Because concatenation of null with any string results in a string C. Because the value becomes boolean true D. Because Access does not allow string concatenation Answers: 1-B; 2-B; 3-C; 4-C; 5-C; 6-C; 7-B; 8-B; 9-D; 10-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. SummaryToday's video from Access Learning Zone covers a common error message in Microsoft Access: "Invalid Use of Null." This is an issue that pops up when you try to send a null value to a function in Access, and I will explain how you can avoid this problem.This discussion started when a subscriber pointed out something important after watching my filter characters TechHelp video. In that video, I show how to use a function to clean up phone numbers by removing unwanted characters. However, if the phone number field is left blank and that value is sent to the filter function, you will encounter the "Invalid Use of Null" error. The subscriber suggested that a quick fix would be to concatenate an empty string onto the phone number before passing it to the function, which works. There are actually several ways to correct or prevent this error, and I will show you a few options. It is important to remember that when creating tutorials like the filter characters video, the main focus is often on demonstrating the core concept, such as filtering out unwanted characters. Sometimes usability issues like this one only become obvious when you start using the solution in real scenarios. That is how a missing check for null values can slip through and only be addressed afterwards. If you have not watched my filter characters video yet, I recommend checking it out so you have full context for what is happening. The filter function is designed to process a phone number and remove all but the digits. For example, if you enter "716-555-3333," the function filters out the special characters and leaves a standard number. The issue arises when the field is blank. If you remove the phone number entirely and try to run the function, Access throws the "Invalid Use of Null" error because it cannot process a null value. A null value in Access simply means there is no data at all in the field; it is not even an empty string. When you try to send this to a function as a parameter, Access does not know how to handle it, which results in the error message. To avoid this, you have a couple of choices. One approach is to check your value up front before running your function. For example, you can test if the value is null using the IsNull function, and if it is, exit the subroutine without calling your filter function. This way, you avoid trying to process a null value entirely. Another option, suggested by the subscriber, is to concatenate an empty string onto the value. When you concatenate anything with null in Access, the result is treated as an empty string, so you avoid sending a null to your function. This solution is quick and effective. Personally, I prefer to use the NZ function. NZ stands for "Null to Zero," but you can use it to convert a null into any value you like, such as an empty string. If you use NZ with the phone number, it turns the null into an empty string, and your function can process it without issue. I also have a complete video dedicated to the NZ function for those interested in deeper details. It is necessary to do this handling outside the function because you cannot send a null value as a parameter to a function in Access VBA. If you try to handle the null inside the function, Access will not even get that far before producing an error. So always check or convert values before passing them into your functions. This type of issue can also arise with other parameters, such as when double-clicking on a list in a customer database. If you double-click on a blank row where CustomerID is null, you will get a similar error. The fix is the same: test for null before proceeding with your code. That way, you avoid trying to process actions on records that do not exist yet. Thanks again to the subscriber for bringing this up and for providing helpful feedback. If anyone else has questions or suggestions, leave a comment or join the discussion in the Access forum on my website. I always appreciate participation from the community. For those interested in supporting the channel and getting more content, there are different membership levels available. Silver members receive access to all extended cut TechHelp videos, a free beginner class each month, and more. Gold members get all of those perks, plus access to all database downloads, the code vault, higher priority for submitted questions, and a free expert class monthly after completing the beginner series. Platinum members get everything from lower tiers and higher priority, plus all full beginner courses for every subject and a free developer class each month after the expert classes. I have courses covering Access, Word, Excel, Visual Basic, and more. No matter your membership status, these free TechHelp videos will always be available as long as you keep watching. 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 ListUnderstanding the Invalid Use of Null error in AccessDemonstrating the error using the filter characters function Explaining the cause of Invalid Use of Null in blank fields Using IsNull to intercept null values before a function call Concatenating an empty string to handle null values Using the NZ function to convert null to an empty string Explaining why null values cannot be passed as function parameters Handling null values in event procedures with IsNull Preventing errors in double-click events with null checks Demonstrating handling null CustomerID values in forms |
||||
|
| |||
| Keywords: FastTips Access invalid use of null, null values, runtime error 94, run-time error 94 PermaLink Invalid Use of Null in Microsoft Access VBA |
