In this article, we look at the 3 most commonly overlooked facts about Null values.
Nulls in MS Access form one of those tricky concepts that even a lot of Access experts have problems dealing with. Understanding Nulls is not a problem if you understand what are ‘not nulls’. That is an easier approach to understanding them. So instead of trying to focus on what are Nulls, you should focus on what nulls are not. But for those who are still looking for a conventional answer, you can consider nulls to be ‘no entry’ fields in a database, where you need to have an entry in all tables and in all fields. Nulls form an important part of the MS Access database thus understanding them is important. So today we will provide you with 3 interesting facts about nulls that you might have overlooked till now.
Nulls are not 0
One of the greatest misconception people have about Null fields is that a null field equals to zero. This can be explained to you with an example. In the immediate window, select Ctrl+G and type
‘? Null = 0’
You will get an answer from the VBA code saying ‘Null’. We started with asking the VBA if unknown equals to zero, and the answer was Null is not equal to or same as zero. You can also try this with another expression, containing a Null.
‘? 4 + Null’
If you still get the answer as Null, it indicates that the answer is not known. This is known as Null propagation, it is kind of a domino effect.
Note: When counting or averaging a field from zero, the treatment of Nulls will differ. Nulls are not a part of operations like sum, average, and count.
Nulls and Zero-length strings are different
VBA makes use of quotation marks for opening and immediately closing a string that has nothing inside. It is its way of representing a string containing nothing. For example – if you do not have any middle name, it is justified to represent it with a zero-length string. But this does not mean that you can represent it with a null, meaning you cannot state that your middle name is not known. For a demonstration of the same, you can opt for adding this in the immediate window.
‘? Len (” “), Len (Null)
This indicates that the first string has zero length, and length of not known is Null(not known).
Note: An Access table can have zero-length strings to distinguish the not known from the non-existent. However, as a user you will not be able to see any difference, thus getting confused.
Nulls and Nothing or Missing are different
These terms might be similar sounding but have very different meanings. Null means not known, ‘Nothing’ by VBA is used for indicating an object that is unassigned, like a recordset which has been declared but has still not been set. ‘Missing’ is used by VBA for referring to that parameter of a procedure which is optional.
Incidents of Access mdb corruption are quite common and in it is not surprising to meet people who encountered data loss due to an Access crash. In case you wish to avoid such a scenario, get hold of a specialized third recovery tool like DataNumen Access Repair.
Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including SQL Server recovery and excel recovery software products. For more information visit www.datanumen.com