Zero Length
By Richard Rost
4 years ago
The Difference Between Null & Zero Length String
In this Microsoft Access video, we're going to learn about Zero Length Strings, the Allow Zero Length property, how a ZLS is different from a Null value, how to set all of this up in our database, and most importantly how to use them each properly.
Simon from Cleveland, Ohio (a Platinum Member) asks: I'm confused. What's the difference between a Null value and a Zero Length String?
Members
Members will learn how to use the format property to display "NULL" or (None) and in color. We'll learn about a problem with DLookup and how it doesn't work properly with zero length strings. I'll share my DLookupZLS function which does work with ZLS. We'll see how short & long text fields handle ZLS differently. We'll learn about different combinations of the Allow Zero Length and Required properties. And we'll learn about two more possible values: Empty and Nothing. This Extended Cut is jam packed with goodies, folks.
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!
Pre-Requisites
Links
Recommended Course
 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, difference between null and empty string, allow zero length, DLookupZLS, DLookup with Zero Length String, ZLS, null string, format, how to tell the difference between null and zero length string, does null = null, what does null equal, empty, nothing, required, variant
Subscribe to Zero Length
Get notifications when this page is updated
Intro In this video, we will talk about zero-length strings in Microsoft Access, including what they are, how they differ from null values, and how to use the Allow Zero-Length property. I will show you how to identify and display zero-length strings versus nulls in your tables, queries, and forms, set up conditional formatting to visually distinguish them, and discuss best practices for when to use each option in your own databases.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
In today's video, we're going to learn about zero-length strings, the Allow Zero-Length property, how a zero-length string is different from a null value, how to set all of this up in your database, and most importantly, how to use it properly.
Simon from Cleveland, Ohio, one of my Platinum members, says, I'm confused. What's the difference between a null value and a zero-length string? Well, Simon, this is one of those questions that's easy to answer, but it's difficult to answer completely, and more importantly, it's difficult to implement correctly in your database. So let me give you some background and then I'll walk you through it.
Before we get started, I just want to address that every time I put one of these videos together, I've been doing this for almost 30 years, but even I still go and Google what everyone else has to say about it. While I was doing my research for this video, I found a bunch of misinformation out there from so-called self-proclaimed experts talking about zero-length strings, saying you should never use them, and that null values are this and that. There is a lot of garbage out there, folks. So I'm going to try and distill it down for you.
The bottom line is do not believe everything you read on a Google search, whether it is Access, computers in general, or life in general. There are a lot of crazy people out there who have the ability to publish information online, whether it's correct or not. So for today at least, let me be your Access fact checker, because this is what I do.
Now I will say that zero-length strings probably are not for beginners. Setting them up is easy, but properly implementing them in your database does take a little knowledge, a little know-how of Access. So while I do cover them briefly in my Access beginner classes, knowing what they are and using them right are two different things. Even then, if you know how to use zero-length strings properly, you have to teach all the users of your database how to use them right too, so that is another level of complexity.
If you are an absolute beginner to Access, I would not even watch the rest of this video. Do not worry about it. Just use null values. Do not even worry about zero-length strings. Come back in a year or so when you have got some Access under your belt and learn the difference.
Now for the rest of you, as far as prerequisites go, I have got a bunch of things I am going to do in this video and some other videos I will reference as I am going. We are going to use a little conditional formatting and stuff later, but for now, just make sure you watch this video first.
What is a null value? What does null mean in Microsoft Access? I want you to know this first before we go into zero-length strings.
So let's talk about the difference between null and zero-length strings. A null value means that this field contains no valid data. The data is unknown. It has not been specified yet. You get this when you enter a new record. You start creating a new record and type in the first name. At that point, the last name is null. You have not entered it yet. You do not know what it is, according to the computer. It is undefined. That is a null value. There is no data.
A zero-length string is different because a zero-length string contains a value. It has a value. You are saying, I know the value, and the value is none. This value does not exist. It is not that I do not know what it is, it simply does not exist.
Zero-length strings are also called empty strings. I have seen them referred to as null string, which I hate. I do not use that term myself. It is confusing. There is a difference between null and a null string. Do not use null string. If you see it somewhere else, that is what they are talking about. I like to use the term zero-length string.
In fact, the term empty also has a separate value in Access, but we will not get into that now.
Zero-length strings are only available for text fields: short text, long text, memo, or hyperlink fields. I do not use hyperlink fields, so really just short and long text fields.
What are some examples of fields where you know the value, and the value is none? The perfect example is middle name. My grandfather, for example, did not have a middle name. His name was Benny Spurduti, and on his birth certificate and his army dog tags, it said "none" where the middle name goes. In that case, that is a poorly designed database (it was probably done on paper back in his day), but his middle name was not "none" - it was literally no middle name. You would not want to put a null value there because that indicates you just do not know what his middle name is (maybe we did not get it when we were surveying him, for example). To have a zero-length string would indicate that we know he has no middle name.
The same thing applies for religion. You can have no religion; you know what their preference is. It is no religion. Title, for a company - President, Vice President, and so on. If you have a one-man band like me, no title. I call myself President CEO because I do have an LLC, but you can have no title.
No social security number: if you are putting in a database, someone might not be American, so they do not have one.
Phone numbers: I do not have a home phone number. I have a couple of different work phone numbers. I do not have a fax number anymore, but I have a cell phone number. So, on a field where I am asked to put in my fax number or my home phone number, I would put in an empty string. Who's still got fax numbers out there? I still cringe every time someone says, can you fax it over to me? Are we living in 1996? The medical industry still uses faxes a lot. I do not know why, but they do.
As I mentioned earlier, it is easy to set up your database to accept zero-length strings. The problem is it is difficult to implement it properly, knowing when to use a zero-length string versus null, and to display which ones are zero-length string versus null. I am going to show you how to do that right now.
Here I am in my TechHelp free template. This is a free database you can grab a copy of on my website if you want to. In here, I have got a customer table where I have got first name and last name. Let's do my perfect example, which is middle name. I am going to add a field right in here. I am going to right-click, insert, now I am going to type in middle name, and we are going to make that short text.
Down here at the bottom, there are two properties: you have got required, and you have got Allow Zero-Length. In older versions of Access, Allow Zero-Length is actually set to no, but I think sometime around Access 97, I want to say, they switched it so the default is yes. They still do not really tell you what a zero-length string is, and even in my beginner classes, I just kind of glance over it briefly because it is not really something that beginners have to worry about.
If you have taken my Beginner Level 1 class, you know that I almost never use the required property. I try not to make stuff required, especially things like first name, last name, middle name, email, and all that stuff. If you make it required and the user does not know what it is (maybe they did not ask the customer, for example), they will just type in garbage if they have to in order to save the record. I always say that no data is better than bad data. You do not want to force someone to have to type in a garbage email address or a garbage phone number, so do not make a field required unless you absolutely have to. Sure, something like order number could be required if you have to have that, but otherwise, try not to put it in.
There are certain fields like first name or last name where, if you are typing in a customer database, you might want to make those required; that is up to you. I always like to leave them blank because you might have a customer whose name is Billy, and you do not get his last name. Or Mr. Jones, and you do not want to prevent the user from putting the record in or put some garbage in.
There is really not much else we have to do at the table level to make something allow zero length. We are pretty much done here. Let's save that, close it.
Right now, if you take a look at the customer table, all of the first names are null. They have no data. If you go down to a blank new record and start typing in something, everything else in here is null as well. Null means we do not have any data yet; it has not been entered yet.
If I go to one of these customers here and I put something in here like "Tiberius" (James Tiberius Kirk), now that middle name field has a value. If I delete that, as soon as I delete that, it now goes back to null. When you delete a value from a text field, that will now register as null.
Let's take my grandpa, for example. Let's put in his first name, Benny (and that actually was his first name, not Benjamin, it was not short for anything, his name was Benny), no middle name (we'll come back to that in a second), and his last name was Spurduti.
Now, how do I indicate that I know that Benny does not have a middle name? I want to make this a zero-length string. To do that, you type in empty quotes: double quote, double quote, and press enter. Now, it looks exactly the same as a null field. That is one of the problems - you cannot tell the difference by looking at them. I will show you in a few minutes how we can, in a form, do a little formatting so I can show you how you can tell the difference. Just looking at it, this is one of the problems with zero-length strings: you have no idea that it is there.
How do we tell it is there? Let's go over to a query for a minute. Let me close this. Go to Create, Query Design. Bring in my customer table. Let's bring in that middle name field. If I run them, there they are. Let's put Jimmy Kirk's middle name in there so you can see: James Tiberius Kirk. I will put mine in there in two minutes. We have got some data. Benny's should be empty string and the rest are all null.
If I run this, there you go. We could do tests to see if something is null or if it is an empty string. In the next column over, I am going to make a calculated field. I am going to say IsNull(MiddleName), just like that. Access is going to call that Expression1. If I run this, there you go. You can see a true/false value: zero or negative one. Negative one is true, zero is false, and you can see that these three show up as not null. It can tell that the Benny Spurduti record has an empty string in there.
We can do the same thing to show the empty strings. I can make another calculated field and say MiddleName = "", just like that. That is Expression2. If I run it, now you can see that is the only one showing up as true. So Access can tell the difference between null and zero-length even if you cannot by looking at it.
We have some tricks though. One of the problems with null values is that null is not equal to anything, including itself. You can test that by saying null = null, like that. That should bring up a value, but if you run it, it says it is null. It is not even equal to itself. It returns a null value. Anything added to, divided by, or involving null - even if it is in an equality - the result is null. It is funny null math. I have a whole separate video on null math. I will put a link to that down below as well. I also have a video for the IsNull function if you have never used that. That is the only way you can check for nulls: a special function that will return a true or false value based on whether whatever you send it is null.
For empty strings, you can check for empty strings with a simple equality. This says if MiddleName is an empty string, it will return a true or false value.
So enough of this query stuff. Most of the time you do data entry, it is with forms. Let's put that middle name on this form. Get rid of a bunch of stuff we do not need. Let's just get rid of all this stuff. Let's add MiddleName in the middle here. Let me get rid of that and buttons. We will leave notes around for a minute. We are going to copy/paste. This will be our middle name, and we will change the control source and name of this guy over to MiddleName. Let's fix our tab order because that is something that annoys me a lot. Move MiddleName right up in there. Now we should be all set.
Save that. Close it. Come back. There we go. James Tiberius Kirk, Benny empty string, Spurduti.
Now, how can I tell that that is an empty string and that is a null value? The Format property of a text field does have an optional second parameter which allows you to display text in that field if there is no value. You can put in here (I will zoom in so you can see it): "@;" and then whatever text you want to have in here if there is no value, like "none". Access will translate the "@", put quotes around it. Save it, close it, open it back up again. As you can see there, it puts "none" in there. But that does not differentiate between null and empty string. You just know there is no value. So that really does not help us.
With some VB coding you can put the IsNull value into that using an event, but that is a lot of work. I will show you how to do that in the extended cut for the members. For the rest of you, it is actually a lot easier just to use a little conditional formatting. Let me show you.
Go to Design View again. Get rid of this format. We are going to use conditional formatting. We are going to make this guy red if it is null, and we will make it blue if it is just empty string so the user will know the difference.
The empty string is actually the easier one. Go to Format, Conditional Formatting, New Rule. If you have never done conditional formatting before, I have got two videos for you to watch. I will put links down below. One is basic conditional formatting, and the second one is using an expression, which is what we are going to do next. Let's say the value is equal to, and then just put empty quotes. That indicates a zero-length string, and we will change this to make it a light blue. If it is an empty string, click Apply. Hit OK. Close it down and restart.
We are good. There is Benny's blue Spurduti. See that? That is an empty string. That one is null, which is not the same thing. You could just stop there if you want to and just teach your users that color means that there is nothing in there.
Now how do you do null? Null is a little trickier because nothing equals null. So you have to add a new rule. You cannot use the field value is, you cannot put the word null in there; I tried it. It does not work. What you can do is say Expression Is, and again, I have a different video you can go watch. In here you will say IsNull([MiddleName]). You have to be careful with this too because sometimes Access puts quotes around MiddleName. Let me see if it does it this time. We will make this one red. Hit OK. Apply. Watch.
Yes, come back in. Watch. I like to leave this mistake in here because this happens to me all the time. That is a bug. You have to make sure (I know most of the time I say you do not need to put the brackets around stuff if you do not have spaces, but this is one of those instances where you do, and I am leaving this in the video because it will happen to you. Trust me, it happens to me all the time.) Now hit OK, make sure you have your brackets there. Now save it and close it and reopen it, and blue and red. See that? That one's null, whereas that one is an empty string.
Unfortunately, you do have to make that unique for each field that you want to show that in. So if you are going to copy that format to different fields, you have to change that function for each one. For example, if you wanted to do first name as well, you can use the format painter to paint over that one. Now you have to go in here, go to Format, go to Conditional Formatting, and change this to FirstName. Sorry, but there is really no way around that.
So now, both of those fields, if I delete Richard, will show up like that. If I put "" (empty string) in there, you can see it is an empty string, which means I have no first name (well, I do).
Something that a lot of people ask me is, how do you switch from a zero-length string back to null? You cannot just delete that; there is nothing in there now. You can make a button to do it, but the easiest way is just put something in there and then go back and delete it. Now it is null. It is silly.
If you do not think your users are going to remember to blank it (such as if I put in my first name and then blank that one), if you do not think they are going to remember that, you could make them a button to do that with. It does involve a little bit of VBA. If you have never done any VBA before, do not panic; I have a video you can watch and I will put a link down below. You can just take a little button and put it right there. Cancel the wizard. Put in here like the word "none". Make that nice and small, or bigger if you want. Now we have a little button here for "none".
Open up its properties, go to the All tab, set this to "NoMiddleNameButton" as the name of it. Right-click, Build Event; that opens up our VBA editor. In here, you will just say MiddleName = "", just like that. Save it, close the form, open it back up again. Let's go to someone else and click on none, and there you go. It is that simple. You have indicated that that person has no middle name. But he does, so I am going to hit escape and put it back.
Now, what uses is this stuff? Let's say maybe you want to do a query and you want to see how many people have no middle name versus how many people you do not have middle names for (the difference there: how many people do you know do not have a middle name versus how many people you just do not have middle names for). That is the difference between null and zero-length string. You could do that easily in a query.
Go to Create, Query Design, bring in that customer table. I am going to make an aggregate query, so turn on the Totals there. If you do not know aggregate queries, I have a video for that; I will put a link down below. Bring in that middle name and change that Total there to Count. Let's count those up. Run it, and I get a three. But looking at the customer table, there is really only one in there that has a value. That is because Count also includes zero-length strings, but that is not always necessarily reliable, so keep that in mind.
Now how do we add up the zero-length strings? Go over here and say MiddleName = "" like that, and change Expression1 to ZLS (zero length string) so it is a little more meaningful. If you change it to Count, you get three, which again is not correct. What Count is actually doing is it counts up the number of records; it does not care what the value is. So if we change this to Sum (because false is a value, so false and trues will add up in a count), if you change it to Sum, however, you will get a negative number representing how many there are, because true is negative 1, false is zero. Count those, you will get three with the Count aggregate, but negative 2 means you have two negative ones plus a zero. We can simply multiply that by negative 1, just put this whole thing times negative 1, and that will give you the actual zero-length string count.
The same thing for counting up the null values. You do not want to use the word "null" there, that is a reserved word. We will say IsNull(MiddleName) * -1, then change this to Sum, and there is your null value. That is the easiest way to get a count of the total number of records you have in here.
If you look at this, you get three as the count of middle names, which means you have three actual middle name values, but two of those are empty values. That is kind of confusing. Forget this one, this is really kind of meaningless. If you want to get a count of how many actual middle names you have, you can do a count of all of the records and then just subtract these two things.
You can bring in a field that you know has a value all the time, like CustomerID. Do a count of that. 29 total records, 26 of those are nulls, two of them are zero-length strings, so you should have one actual middle name, which is Tiberius. We can do that with a little math. Let's refer to CustomerID as NumCust, for example, so we do not have to call it "Count of CustomerID". Now when we run this, we get those three values: Number of Customers, Null Value, and Zero Length String.
Unfortunately, it is not easy to put all this in the same query. You could put it over here, but you cannot refer to NullV and ZLS because they have not been calculated yet. But we could save this. Just call it Q1. Then we can make another query off of that one: Create, Query Design, go to Queries, bring in that Q1. Bring in all this stuff if you want to, and then right here you could say ActualMiddleNames: NumCust - NullV - ZLS, and that will give you a count of how many actual middle names you have.
As you can see, there are a bunch of steps to it, but if in your database you care about these things (if you are doing statistics or whatever and you have got to know how many of whatever particular field you do not have data for versus how many you do have data for, but the data is "none"), then this is what you have to do. I will save this one as Q2 for you Gold members. If you want to download that, you have both of those queries.
If you are interested in learning more stuff like this, I cover a lot more about zero-length strings and null in the extended cut for the members. First, I am going to show you how to use the Format property to display "none", "null", "zero length string", or whatever you want to display in there, and change the color if you have got a null value or a zero-length string. We know how to use conditional formatting and that is just fine, but if you want something a little more visible for your user, you can add that as well. You can use both at the same time. It requires a little bit of VBA code to be able to put an actual text there.
Then we are going to talk about a problem with DLookup. If you use the DLookup function at all, it does not handle zero-length strings. It will only return a null value. So I wrote my own function called DLookup0LengthString, which of course is free for the members.
Then I am going to show you how short and long text will actually handle zero-length strings differently before you save the record. That is interesting. Sometimes it will return a null, sometimes it will return a zero-length string. It depends on what you are doing.
Then we are going to talk about some combinations between Allow Zero-Length and Required, the Required property. If you have something that is required but zero-length strings are allowed, you are going to get something different than if it is not allowed, so I cover all the different combinations of those two properties together.
Not to make your head spin even more, there are two additional values you might have to know. One is called Nothing and one is called Empty. A variable can be Empty, which basically means you have not assigned it a value yet, and that is for variables, not for fields. So what you do in VBA is different. A variable in VBA can be zero-length string, null, Empty, or Nothing and there are lots of different combinations. We will cover all that in the extended cut for the members.
Remember, Silver members and up get access to all my extended cut videos and there are lots and lots of hundreds of them by now. Gold members can download all these crazy databases that I make in the TechHelp video.
Do not forget to watch the null video if you have not already. Here are some other videos I mentioned. I will give you links to them. Watch my null math video. This is where I show what happens when you add null values to anything else. I have a video for the NZ function. NZ is called Null to Zero, which basically, if you have a function like DLookup that returns a null value, this will convert it to zero or whatever else you want to convert it to, so you do not get an error.
This is one of my more popular videos: "Invalid Use of Null". I get tons of people asking me about this one. Mike Wolf over at No Longer Set has a great article about null and IsNull and all kinds of other stuff, including NZ on his website. Check this out. I will put a link down below.
If you have not used conditional formatting before, I have a video for that, as well as conditional formatting for null values using the expression that I used earlier.
Finally, for more on aggregate queries, it is how we got that sum and count. I have an aggregate query video. You will find links that you can click on to all of these videos. I have those articles on Microsoft's website, link down below too.
These are all free videos on my website. You can check them out whenever you want. I have videos on pretty much everything. Once again, if you want to learn more about zero-length strings and null, check out my extended cut video for the members.
Remember, Silver and up, get access to everything. What are you waiting for? Join today.
That is your TechHelp video for today. I hope you learned something, and I will see you next time.Quiz Q1. What is the primary difference between a null value and a zero-length string in Microsoft Access?
A. A null value means the data is unknown; a zero-length string means the value is known to be "none".
B. A null value and a zero-length string are the same thing.
C. A zero-length string means the data is corrupted.
D. A null value can only be used in numeric fields.
Q2. In which field types can zero-length strings be used in Access?
A. All field types, including numbers and dates
B. Short text, long text (memo), and hyperlink fields
C. Number and currency fields only
D. Attachment fields
Q3. Why should beginners to Access generally ignore zero-length strings at first?
A. Zero-length strings require advanced hardware.
B. Beginners should not use text fields.
C. Using and implementing zero-length strings properly requires more advanced Access knowledge.
D. They are only needed for primary keys.
Q4. What does the "Allow Zero-Length" property in a text field do?
A. Prevents any data from being entered into the field
B. Allows the field to accept empty quotes ("") as a valid value
C. Automatically fills in the field with "none"
D. Requires the field to always have a value
Q5. If you want to indicate that a person truly has no middle name (not just missing data), which should you use?
A. Enter "none" as text
B. Use a null value
C. Enter a zero-length string ("")
D. Leave the field required
Q6. What happens if you simply delete the contents of a text field in Access?
A. It becomes a zero-length string.
B. It becomes null.
C. The field gets locked.
D. It becomes "empty".
Q7. How can you visually indicate the difference between null and zero-length string fields for users in a form?
A. Only with the Format property
B. By using conditional formatting to color-code the fields
C. By changing the table primary key
D. By locking the field
Q8. Which function can you use in a query to test if a value is null?
A. IsEmpty()
B. IsNull()
C. IsZeroLength()
D. EqualsNull()
Q9. Why is comparing null with anything else (including itself) in Access challenging?
A. Null is always less than zero.
B. Null is equal to any string.
C. Null does not equal anything, not even itself.
D. Null can only be compared in VBA.
Q10. How can you test for a zero-length string in a query?
A. Using the expression FieldName IS NULL
B. Using the expression FieldName = ""
C. Using Count(FieldName)
D. Using FieldName > 0
Q11. What is a good practice regarding the Required property in Access tables for fields like first name, last name, or email?
A. Make all fields required to ensure data completeness.
B. Never make fields required, not even primary keys.
C. Avoid making them required unless absolutely necessary to prevent users from entering garbage data.
D. Only use required for number fields.
Q12. Which of the following is true about the Count aggregate function in Access queries when dealing with zero-length strings and nulls?
A. It ignores both nulls and zero-length strings.
B. It counts only those fields with non-null, non-zero-length values.
C. It counts all records regardless of value, including zero-length strings.
D. It only counts fields containing numeric data.
Q13. What is one way to let users easily set a field to a zero-length string using forms?
A. Lock the field
B. Add a button with VBA code that sets the field to ""
C. Make the field required
D. Rename the field
Q14. If you want to count actual (non-null, non-zero-length) values in a field, what would you do?
A. Count only the non-null values
B. Subtract both the null and zero-length string counts from the total record count
C. Count only zero-length strings
D. Use the field's primary key as the only count
Q15. Why might you want to distinguish between a null and a zero-length string in your database?
A. For customizing input forms only
B. To control Access licensing costs
C. For data analysis to distinguish between unknown and "none" data
D. There is no reason to do so
Q16. What must you remember when copying conditional formatting that checks for null from one form field to another?
A. Nothing, it works automatically
B. You must change the VBA code in Access settings
C. You must update the field name in the expression for each field
D. It only works for numeric fields
Q17. Which function converts null values to another value (such as zero or empty string) in Access calculations or queries?
A. IsNull()
B. NZ()
C. Sum()
D. NullToValue()
Q18. When adding conditional formatting to distinguish null and zero-length string, what basic steps are involved?
A. Add two rules: one for Field="" (zero-length), one for IsNull([Field]) (null), each with a different color
B. Change the underlying table data type
C. Add a subform to the form
D. Only enable the Format Painter tool
Q19. Which of the following is NOT covered in this video?
A. How to use conditional formatting to distinguish null vs zero-length string
B. How to set the Allow Zero-Length property in a table
C. How to create SQL Server triggers
D. How to use aggregate queries to count null and zero-length strings
Q20. What does the NZ() function do?
A. Returns the database name
B. Converts a null value to zero or another value you specify
C. Creates new fields in a table
D. Finds zero-length strings
Answers: 1-A; 2-B; 3-C; 4-B; 5-C; 6-B; 7-B; 8-B; 9-C; 10-B; 11-C; 12-C; 13-B; 14-B; 15-C; 16-C; 17-B; 18-A; 19-C; 20-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 video from Access Learning Zone focuses on understanding zero-length strings and the Allow Zero-Length property in Microsoft Access. I'll explain how zero-length strings differ from null values, how to set up these features in your database, and – perhaps most importantly – how to use them correctly.
This question comes up quite a lot. For instance, what's the real difference between a null value and a zero-length string? The answer isn't complicated, but actually putting it into practice – and making sure people use it correctly – can be tricky. There's also quite a bit of misinformation about this topic online. While researching, I've come across so-called experts making sweeping statements against zero-length strings or mischaracterizing nulls, so let me be your reliable source here. Don't take everything you read online at face value, especially when it comes to Access. People can post anything, whether it's right or not.
Zero-length strings aren't usually something beginners need to worry about. While setting them up is straightforward, applying them properly takes some familiarity with Access. That said, if you're just getting started, feel free to stick with nulls for now; you can always revisit zero-length strings when you have more experience.
For those with a bit more background, here's what you need to know:
A null value means the data in that field is currently unknown or hasn't been specified. For example, when you add a new record and leave last name blank, it's null – the system just doesn't know what the value is.
A zero-length string, on the other hand, actually holds a value. It represents the scenario where the answer is explicitly "none" or "does not exist" rather than "unknown." Sometimes, this is also referred to as an empty string. Some sources use "null string," though that's confusing and best avoided. In Access, the term "empty" has its own specific meaning, but we won't cover that here.
Zero-length strings can be used in text fields – short text, long text, and hyperlinks (although I tend not to use hyperlinks much).
What are some good real-life examples? The classic one is middle name. If you know someone truly has no middle name (not just that you don't know it), then a zero-length string is appropriate. The same logic applies to fields like religion, title, Social Security number, or phone number. When you know the value truly doesn't exist, use a zero-length string. Otherwise, use null if the information is just unknown or not yet provided.
Setting up your table to allow zero-length strings is easy. In your table design for a text field, find the "Allow Zero-Length" property and set it to Yes. In older versions of Access, this defaulted to No, but nowadays it's generally set to Yes by default.
While you're here, you'll notice a "Required" property as well. Personally, I rarely set fields like first name, last name, or email to required because, if users are forced to enter something, they'll often just type in junk to get past the restriction. It's better to have missing data than bad data. Of course, for things like order numbers, required fields make sense.
After setting up "Allow Zero-Length," save your changes. When you enter data, a blank field will be null by default. But you can enter a zero-length string by typing two double quotes and pressing enter. Visually, blank and zero-length string fields look the same, which leads to another challenge: telling them apart. Fortunately, Access can distinguish between the two in queries.
For example, you can create a query that includes the field in question and then use calculations to check if a value is null (using IsNull) or a zero-length string (by testing if the field equals ""). The IsNull function returns True (-1) if the value is null and False (0) otherwise, while testing for "" returns True for zero-length strings.
One quirk with nulls is that null is not equal to anything, not even itself. So, things like "null = null" don't return True; instead, they return null. To check for nulls, you always need to use IsNull. For empty strings, though, a simple equality check works fine.
In forms, you'll usually want to help users see the difference between null and zero-length string fields. Access's Format property for text boxes lets you specify a default text when a field is blank, but it doesn't distinguish between null and zero-length strings. For better differentiation, you can use conditional formatting. For example, you could set up conditional formatting rules to display zero-length strings in blue and nulls in red. When building the rule for a field to show blue for empty strings, just compare the field to "" and pick your format. For nulls, you'll use an expression with IsNull and make sure to use brackets around the field name.
Keep in mind, though, you need to adjust the condition for each field you want to format – the rules have to reference the correct field name.
Switching a field back from zero-length string to null isn't as simple as just backspacing or deleting. You can add a command button to your form to assist with this process using a little bit of VBA. This way, you can offer users a button to set a field to a zero-length string easily. Deleting and then re-entering data restores a field to null.
If you're analyzing your data, you might want to know how many records have nulls, how many have zero-length strings, and how many have actual values. You can do this with aggregate queries. Use calculated fields to test for each condition (IsNull or = "") and use the Sum function in your query – multiplying your test by -1 converts True/False values into easy-to-read counts. You can then combine these counts with a total count of customer records to determine exactly how many of each type you have.
If you want to take this further, in the Extended Cut for members, I show how to use the Format property and VBA to display custom messages or colors for nulls and zero-length strings – something a step above what conditional formatting provides. I also explain how to deal with functions like DLookup, which don't return zero-length strings, and demonstrate a workaround with a custom function. There are additional insights about how short and long text handle zero-length strings before saving, as well as the interplay between the Allow Zero-Length and Required properties. Finally, I touch on concepts like Nothing and Empty that matter for variables in VBA but not generally for fields in tables.
If you want a deeper understanding, I recommend watching my other videos covering nulls, IsNull, the NZ function, "Invalid Use of Null," and aggregate queries. All of these are available for free on my website.
For the extended cut and downloadable sample databases, consider a membership. Silver and Gold members get access to even more content and resources.
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 Difference between null values and zero-length strings
Understanding zero-length strings in Access
Setting the Allow Zero-Length property
Examples of fields suitable for zero-length strings
Adding zero-length string support to a table
How to enter a zero-length string in a text field
Testing for null vs zero-length string in queries
Using IsNull function to identify null values
Query expressions for detecting zero-length strings
Demonstrating "null math" limitations in Access
Displaying zero-length strings and nulls on forms
Applying conditional formatting for empty strings
Conditional formatting for null values using expressions
Adding a button to set a field to a zero-length string
Using VBA to set zero-length string via button
Counting nulls and zero-length strings in queries
Aggregate queries for field value statistics
Calculating actual values vs empty or null entries |
