HasValue
By Richard Rost
3 years ago
HasValue Function in VBA to Check Null, Empty Values
In this Microsoft Access tutorial, you will learn how to create a custom HasValue function using VBA. This function is specifically designed to check for empty and null values in short and long text fields. This function is great for when you don't know what type of field you're dealing with, and you're tired of always checking for both.
Mason from Schaumburg, Illinois (a Platinum Member) asks: How can I understand the difference between an empty value and a null value in Microsoft Access? I have a button on a form to check if a field is blank, but when a user deletes a value in a record, I noticed that the behavior differs for short text fields (which become null) and long text fields (which become empty). Can you explain what's going on?
Members
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!
Prerequisites
Recommended Courses
Code Vault:
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, HasValue function, Microsoft Access tutorial, VBA, custom function, check null values, check empty values, short text field, long text field, checking field types, deleting values, button demonstration, step-by-step walkthrough, developer courses, Variant
Subscribe to HasValue
Get notifications when this page is updated
Intro In this video, we'll talk about how to create a custom HasValue function in Microsoft Access using VBA to check if form fields are either null or empty strings, with a special focus on how short text and long text fields behave differently when values are deleted. We'll go over the differences between null and empty values, the use of Variants in VBA, and how to simplify your code with a single function to check for both conditions in your Access forms.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today we're going to create a function called hasValue that you can use in your forms to check for values that might be null or empty strings, because it's a pain to have to always check for both of them, and long text fields and short text fields behave differently. Of course they do.
Today's question comes from Mason in Schaumburg, Illinois, one of my Platinum members. Mason says, "How can I understand the difference between an empty value and a null value in Microsoft Access? I have a button on a form to check if a field is blank, but when a user deletes a value in a record, I notice that the behavior differs for short text fields, which become null, and long text fields, which become empty. Can you explain what's going on?"
Yes, Mason, it's confusing. If you have a record open and you delete a short text value, it becomes null even if you don't leave the record and come back. If you delete a long text field, it becomes an empty string.
Let me demonstrate.
Before we get into it, this is a developer-level class. What does that mean? That means you'll need to know some VBA. If you've never done any programming before, go watch this video. It's about 20 minutes long and will teach you everything you need to know to get started.
Watch my video on null values so you understand what that means. Watch my video on zero-length strings so you understand the difference between those two things. You'll need to know how to make an if-then statement. Also, go watch my video on creating a custom function, which we'll be doing today.
These are all free videos. They're on my website, and they're on my YouTube channel. Go watch them and come on back.
Here I am in my TechHelp free template. This is a free database. You can download a copy from my website if you want to. Let's go to the customer form.
Here's the customer form. On here we've got a short text field, we've got a bunch of first names, last names, whatever, and we've got a long text field, formerly known as a memo field, right over here.
I'm going to make a button real quick so we can check to see what the value is, if it's null or an empty string for each of these things.
I'm just going to hijack this Orders button right here. I will just call it the Check button and right-click Build Event.
In here, we're going to say:
If IsNull(FirstName) Then
MsgBox "First name is null"
End If
If FirstName = "" Then
MsgBox "First name is empty"
End If
If it's neither of those things, we won't get a message box if it has a value in it.
Click the button, nothing happens. It's got a value.
If I delete that value and hit the button, it's null. First name is now null, even though I haven't left the record yet. It's null.
If I leave the record and come back, it's still null.
In fact, the only way you can make a short text field intentionally an empty string is to put an empty set of double quotes in there. Now, it's empty.
Generally, if you put some other value in there and then delete that value, it's now null. So short text fields: delete the value, it goes to null.
Now let's check the same thing for our notes field.
Notes is a long text field. Save it, come on back.
Click the button, nothing happens, which means it's got a value.
Delete what's in there, hit the check button. It's empty. See, it treats it differently. Access treats long text fields differently than short text fields.
I'm going to put a value back in there, nothing happens. If I take the value out, it's back to empty.
Now, watch this. If I leave the record and then come back to it and check it, it's null now. See, it's weird. There are technical reasons why that happens like this, but it's a pain, because if you've got buttons in here or other events that are checking the values of these fields when the user's doing stuff, you always have to say "if Notes is null" or "if IsNull(Notes)" or if it's empty. You have to always check all those conditions, and it's a pain.
That's why we're going to write a function to just check both of those situations for any text field. We're going to check to see if it's null and we're also going to check to see if it's empty, and we're going to return a value based on whether or not it has a value. If it's not, or it doesn't, we'll say it has no value.
Here's what we do. We're going to go to our global module. If you don't have one, create one, but watch my Create a Function video.
It's just literally Create, then Module (not Class Module, Module), and you put your code in here.
Down here we're going to say:
Public Function HasValue(x As Variant) As Boolean
We're going to send in some value. We'll call it x; it doesn't matter what it is. We're going to make it a Variant.
What is a Variant? There are different types of values: strings, long integers, integers, currency, Boolean for Yes/No. Variant means it can be of any type; any of those are valid fields. The reason why you have to use a Variant here is because you cannot send a null value to a function. It will generate an error, and we don't want that, but if it's a Variant, that's allowed.
You have to use Variant for this function, and I cover Variants in a lot more detail in my full developer classes.
Now, HasValue is going to return a value, right? It's going to tell you whether the value you sent into it is true or false, or is null or empty. So we're going to say As Boolean, so we're going to return a true or false value. You can put that in your notes here:
' This function will return False if the value x is null or empty string. Otherwise, it will return True because it has a value.
' There are other types of objects and variables that can be empty or things like that, but this works for text boxes.
So:
If IsNull(x) Then
HasValue = False
ElseIf Trim(x) = "" Then
HasValue = False
Else
HasValue = True
End If
That's it. Check to see if it's null. If it's not null, check to see if Trim(x) is an empty string, and if not, we're good to go.
Let's test it. Save it.
Let's go back over to our forms code. Here's a trick: since I'm in my VBA editor, the window behind this one, if you close this guy, is going to be that form's module. I usually don't leave the Project Explorer open because I usually work with one thing at a time, or two at most.
Let's get rid of this, and we're going to use our HasValue function.
So we're going to say:
If HasValue(FirstName) Then
MsgBox "First name has value"
End If
If HasValue(Notes) Then
MsgBox "Notes has value"
End If
Save it. Always good to throw in a Debug -> Compile from time to time.
Let's click on it. Let's click the button to check.
Neither one of them had a value, so I'm not getting anything. Let's put something in the first name field. Check. First name has a value. Let's put something over here. Check. First name has a value, notes has a value.
Now let's get rid of this. That should be null now, so only notes has a value.
Let's put something back in here and let's get rid of this and then check. First name has a value. Notes is not showing up with a value because it's empty right now, but it's going to check for empty or null.
If I leave it and come back to it, it still catches that it's null.
So you don't have to have multiple checks or guesses for whatever values you have. You can write different code for different types of values if you want to. If you want to make a numeric one, if zero is your blank, you can make an isZero function or whatever.
This is just another Lego, another tool in your box.
There you go. There is your hasValue code. Gold members, I'll throw this in the code vault and you can go get a copy if you want, or you can download the database off the website.
If you like learning this kind of stuff, if you like learning with me, if you enjoy learning VBA and you like my style and want more lessons, I have tons of developer lessons available on my website. There is a link right there, or you can scan the pretty little QR code down there and go to my website to check them out. I've got like forty some levels of different Access lessons available.
That's going to do it for today, folks. That is your TechHelp video. Hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main purpose of the hasValue function presented in the video?
A. To check whether a field contains a null or empty string and return a Boolean result
B. To automatically populate empty fields with default values
C. To sort records in a form based on whether they have values or not
D. To convert all null values in a table to empty strings
Q2. In Microsoft Access forms, what is the difference in behavior between deleting a value from a short text field versus a long text field?
A. Short text fields become empty; long text fields become null
B. Both become null
C. Short text fields become null; long text fields become empty
D. Both become empty
Q3. Why is the Variant data type used as the parameter for the hasValue function?
A. It only accepts string values
B. It can accept any type of value, including null, without causing errors
C. It automatically trims all input values
D. It prevents the function from returning Boolean results
Q4. When does the hasValue function return False?
A. When the value is an integer greater than zero
B. When the value is either null or an empty string
C. Only when the value is null
D. Only when the value is an empty string consisting of spaces
Q5. Which built-in VBA function is used to check if a value is null within the hasValue function?
A. IsEmpty
B. IsNothing
C. IsNull
D. IsBlank
Q6. Which function does the hasValue function use to eliminate leading and trailing spaces from text before checking if it is empty?
A. RemoveSpaces
B. Replace
C. LTrim
D. Trim
Q7. What is the primary advantage of using a function like hasValue in your Access VBA code?
A. It makes it easier to standardize checks for whether a field is null or empty across multiple forms and situations
B. It automatically converts all null values to zeros
C. It forces users to enter a value in every field
D. It sorts the fields alphabetically before processing them
Q8. According to the video, if you want to create a similar function for numbers where zero is considered blank, what should you do?
A. Replace Boolean with Variant in the function
B. Write a different function, such as isZero
C. Change all fields to text type
D. Use the same hasValue function with no modifications
Q9. If you enter a value in a short text field, then delete it without leaving the record, what will the value of the field typically be?
A. An empty string
B. Null
C. Zero
D. The previous value remains
Q10. What is the recommended VBA editor practice mentioned in the video after making changes to your code?
A. Save and immediately close Access
B. Always run a Debug -> Compile before testing
C. Only test your code in the main database window
D. Use the Immediate window to run all new functions
Answers: 1-A; 2-C; 3-B; 4-B; 5-C; 6-D; 7-A; 8-B; 9-B; 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.Summary Today's TechHelp tutorial from Access Learning Zone focuses on creating a function in Microsoft Access called HasValue. This function helps you check whether fields in your forms are null or empty strings. The need for this arises because short text and long text fields in Access behave differently when users delete their content, which can make value checking a challenge.
Let me explain the issue first. In Access, if you delete the value from a short text field while editing a record, that field becomes null immediately even if you have not left the record. On the other hand, deleting the content of a long text field (previously called a memo field) results in an empty string rather than null, unless you leave the record and come back, at which point it might become null. This difference can cause confusion or errors if your forms or code are checking for blank fields.
Before moving forward, keep in mind that this lesson is for developers who are comfortable with VBA. If you are not up to speed with concepts like null values, zero-length strings, writing If-Then statements, or creating custom functions in Access, I strongly recommend watching my free introductory videos on these subjects first. These resources are available on my website and YouTube channel and will give you a solid foundation for today's topic.
To demonstrate the behaviors, I use the TechHelp free Access database template. You can download this template from my website if you want to follow along in your own copy. In the customer form, you will find a short text field (for example, a name) and a long text field (such as a notes area).
I added a button to the form to test and display whether these fields are null or contain empty strings. When checking the short text field, if you delete its value and use the button to check its state, Access will report it as null. Deleting and then re-entering an empty string (double quotes with nothing between them) is the only way to intentionally set a short text field to an empty string. With the long text field, if you delete the contents, Access sees it as an empty string, and only after leaving and returning to the record might it change to null.
Because of these differences, checking if a field is 'blank' means always having to test both possibilities: is the value null, or is it an empty string? Writing this logic every time becomes tedious. To simplify this process, I show how to create a reusable function called HasValue.
To create this function, open a global code module in your Access database. If you do not have one, create a new module (not a class module). This is where you will write your code for HasValue.
The key point is to make the function accept a Variant type, not a specific data type such as String. Variants can represent any kind of data, including nulls, and allow your function to work with all field types. The function returns True if a value is present (meaning the field is neither null nor an empty string) and False if the field is blank by either definition. You should also trim the value to remove unintentional spaces before making the empty string check.
Once HasValue is saved in your module, you can use it anywhere in your VBA code to quickly check any field. For example, you might use it to display a message box when a certain field has a value, or to control what actions your form takes based on user input. This approach eliminates the hassle of always writing separate checks for null and empty string values, making your code cleaner and more consistent.
You can tailor similar functions for other data types as needed. For instance, you might want a function that treats zero as blank for numeric fields. The HasValue function is just one more useful tool to add to your toolkit as you develop more advanced Access databases.
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 Differences between null and empty string in Access
Behavior of short text fields when cleared
Behavior of long text fields when cleared
Creating a button to check field values
Using IsNull to test for null values
Checking for empty strings with = ""
Demonstrating field value changes without leaving record
Explaining variants in VBA functions
Writing the HasValue custom function
Using Trim to handle spaces in text checks
Implementing HasValue in a form event
Testing HasValue with various field scenarios |
