Technology Training Community

Today, I am going to examine Null when associated with text fields.  I will continue this subject in my next post by looking at Null associated with number fields.  A Null value is data that is missing or unknown.  In Microsoft Access, Null is treated as unknown data.   The data exists but Access does not know what it is.  Realize that this is different from an empty string, which means there is no data. If you keep this in mind, the following will be easier to understand.


When you enter “=Null” as your criteria for a field, you are asking Access to return the data values that equal Null.  However, since Access does not know what Null represents, your results will not contain records with Null in the specified field.  Access is really saying, “I cannot tell you if anything equals Null, I don’t know Null”. 

What do you enter as your criteria if you what to return or exclude records that contain Null in a specified field?  You would enter Is Null or is Not Null.  You may have notice that Access does this for you.  If you enter Null as the criteria, Access with change the criteria to Is Null and if you enter Not Null, Access will change it to Is Not Null. I realize the term “Is Null” seems to be synonymous with the term “= Null”, but to Access there is a difference.  You are no longer looking to match the data in the criteria field, now you are looking to see if the field is unknown.  Access knows that the data in the field is unknown. Access does not know what the data in the field equals.

As mentioned previously, Access makes the necessary corrections when Null or Not Null is entered as the criteria.  So what is the issue?  The mistake that I often see people make is the following:
Example:

From the table above, I want to return the people who are not from Pennsylvania.  If I enter Not “Pennsylvania” as the criteria, I would get Sally, George and John returned.  Why isn’t Mary returned?  We do not know if she is from Pennsylvania.  So when using MS Access she would not be returned.  Access does not know where she is from; the data is NULL or unknown.  If I want to return the people I know are not from Pennsylvania, as well as anyone whose state of residents is unknown, the criteria needs entered as Not “Pennsylvania” or Is Null.  I would not get Sally, George, John and Mary returned.