In simple English, NULL means nothing – absolutely nothing. In MS Access too, NULL means the same….no Value. NULL means absence of any value whatsoever. NULL is different from a zero Value. In fact, NULL is also different from a Zero length String (ZLS), though they appear the same visually.
The presence of NULL value indicates that maybe you have no value to add in the table, or simply the value is unknown. Furthermore, you cannot compare NULL value i.e. Comparing two NULL value will give you NULL, since by definition you have no value to compare with.
How is NULL different from ZERO Length String (ZLS)?
Zero Length String as the name suggests is a string of zero length. So, technically, you have a string but of zero length. Whereas NULL is absence of value, as mentioned earlier. Lets understand the meaning of NULL and ZLS from MS Excel point of view.
Understanding NULL and ZLS in “MS Excel Parlance”…
Consider the screen shot below :
If the value in column A is equal to value in Column B, the value you want to be returned in column C is “Equal”, else you want blank - hence you specify ‘value if false’ as “”
But in the last row, for some reason, you do not write any formula.
On using the above IF statement in column C, you find that row 2 & 3 returns “Equal” whereas the row 5 & 6 shows empty cells.
Finally, in the column D you insert a ISBLANK() formula to determine which cell is empty or which is not. On writing this formula, you find all rows are FALSE except the last row though it looks same to the previous row.
In short ISBLANK() formula helps us to identify which cell is NULL (Cell: C5) and which is Zero Length String (C4).
Hopefully by using this familiar example in MS excel, you got a fair idea of how NULL value is different from ZLS . Also you got an idea that there are in-built functions to identify and deal with null values.
NULL related functions in MS Access??
So the next question that may come up in your mind is whether there are any specific in-built function in MS Access to handle NULL values. The answer is ‘YES’ and those are as follows:
- IS NOTNULL
However, You’ll explore the above functions in detail as you go further.