Nulls and Other Nothings - An Explanation

It is easy to be confused by the various ways in which a lack of data can be represented in various situations. Terminology, both official and æunofficialÆ, doesnÆt help - for example ônull stringö is a contradiction in terms û strings exist and are, therefore, not null. What follows is a slightly expanded version of a reply I gave to a question which offers some explanation.

At a very simple level, things can either exist or not. If they exist they may or may not contain meaningful data. Things which do NOT exist may be null, nothing or missing. Things which do exist but have no meaningful content may be empty.

Taking each in turn:[ol][li]Null is a relational database concept which represents total absence of data in a repository being queried. VBA implements a method of dealing with nulls which can sometimes be confusing but, unless you specifically ôcreateö them, they rarely exist in Office Applications other than Access.

Very briefly, as this forum is not generally Access-specific, fields returned from Queries, and controls in both Forms and Reports have the potential to be null and code which references them should check for this unless it is known that they always exist û they are, or are bound to, for example, Required fields.

I am not aware of anywhere in either Word or Excel where nulls occur naturally but Excel confuses the issue by having a [color blue]#NULL![/color] error which can be raised if reference is made in a worksheet formula to the intersection of two ranges which do not intersect (in VBA the result is Nothing).

To check for something being null, use the [color blue]IsNull[/color] function. There is a NULL keyword but it cannot be used in comparisons. "If Var1 = Null" will ALWAYS return FALSE regardless of Var1.[/li]

[li]Empty is, I'm afraid, another slightly confusing term. A cell in an Excel worksheet can be empty and can be checked as such by using the [color blue]IsEmpty[/color] function in VBA (but note that in a worksheet you use [color blue]ISBLANK[/color] to test for the same condition). A variant (i.e. untyped) variable can also be empty meaning un-initialised and can also be checked with the IsEmpty VBA function.

Note that typed variables (that is, all variables other than variants) which do not refer to objects and which have not been set, are initialised in VBA, depending on their type, to either zero or a zero-length string, but are not empty (or null). [/li]

[li]Nothing is a slightly different animal. Object variables which do not currently reference an object have a ævalueÆ of Nothing. More importantly, setting an object variable to Nothing doesn't just affect the variable; if it is the only variable referencing the particular object, it disassociates the object from the code and releases all related system resources.

To check if an object reference is nothing, use the construct: If Object_var [color blue]Is Nothing[/color][/li]

[li] Finally, Missing. This only refers to Optional parameters. If an optional parameter to a function is declared with a type (e.g. "Optional Var1 As String") then, if it is not supplied by the calling code, VBA provides a value of the appropriate type - zero for numeric types, a zero-length string for string types and it will NEVER be Missing.

A parameter can only be Missing if it is untyped (declared as "Optional Var2" or "Optional Var2 As Variant"), and will only be Missing if it is not supplied by the calling code. To check if a parameter is missing use the [color blue]IsMissing[/color] function.[/li][/ol]