Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
Tips & Tricks

New Tips Added Weekly!
Click here to get on our Mailing List

   
 

NULL v. Zero Length String

What's the difference between a NULL value and a Zero Length String

Q: What is the difference between a NULL value and a Zero Length String?

A: The difference between a NULL and a Zero-Length String (ZLS) is that a NULL has no value, whereas a ZLS has been specifically given a value of blank. It's basically the difference between saying, "no value" or "no value YET."

Furthermore, this distinction usually only comes about in Access when you're dealing with VBA programming and you either set the ZLS value yourself, or return the value as part of a function call, such as:

   LastName = ""
   FirstName = NULL


When you're entering data in a table or form, you will usually either specify some data, or no data. Your fields will either have a value, or they will be NULL. Even if you type in a value and then come back later and delete it, it will still be NULL. Manually deleting data from a table sets it to NULL, so the average user will never really have to worry about what a ZLS is.

However, let's say you're creating a database where you need to know more specific information on people. Let's say you're tracking first, middle, and last names, and you NEED to know whether or not someone has a middle name. You could set their middle name equal to a ZLS to indicate that they have NO middle name, as opposed to not knowing what their middle name is. Statistically it could make a difference. You could make a button, for example, that just says:

    MiddleName=""

You can check for ZLS by looking for "" - an empty string. You can check for NULL by using the IsNull function. In your VBA code this will often result in looking for both:

    If IsNull(LastName) or LastName="" Then
        ' Do some stuff
    End if


You can prevent ZLS in your table by changing the Allow Zero Length property of a text or memo field to No/False. This will prevent any ZLS values from getting in your table.

Do you need to worry about any of this? For the most part, no. The difference between NULL and ZLS is mostly theoretical, and unless you're specifically coding your database to CARE about the difference between the two, you shouldn't have to worry about it. However, because some functions might return a ZLS, you might want to check for both in your VBA comparisons. Usually this distinction only matters when you need to care about the difference between NO value and no value YET.

Also, Access will index NULL values and ZLS values differently, which is a whole different topic of discussion.

 

 


By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

Click here to sign up for more FREE tips

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP