[AccessD] Null and Empty (was NOT Query).

Susan.
You've got it back to front. 

And "Empty" is a concept which applies primarily to Variants.  It is the VarType of a Variant 
which has never been initialised. 

As far as fields are concerned....
There is no such concept as "initialisation" of a data field
Some people use the terms Null and Empty interchangeably when talking about data fields. 
Note however that an "empty string" (zero length) is different to a Null.
Any field type can be set to Null.  Only a text field can be populated with an Empty string, 
and in Access, only when  you set the field property to "Allow zero length strings".   

Now back to Variants....
Remember that Variants have both a Value and a Type.

If a variant has not been initialised/used, it has not yet been assigned a Type and is 
therefore of type "Empty". (VarType = 0).  An empty variant will return 0 if treated as a 
numeric and an empty/zero-length string ("") if treated as a string.  It will not return Null !!!!

A variant of type Null (VarType = 1) is a variant which has been intialised at some stage (ie 
assigned a value and a type) and has subsequently been set to Null. (ie the Value has been 
removed) or one which has been initialised as Null.   It will NOT return 0 or "" when treated 
as a numeric or as a string.

Try this to see the differences:

Function CompareNullAndEmpty()
Dim a As Variant
Debug.Print a, a + 1, "-" + a + "-", "-" & a & "-", VarType(a)
a = Null
Debug.Print a, a + 1, "-" + a + "-", "-" & a & "-", VarType(a)
End Function

--
Stuart


On 31 Mar 2009 at 11:02, Susan Harkins wrote:

> Well Arthur, in a nutshell, it has to do with initialization, which confuses 
> the hell out of me, even though I'm supposed to be smart enough to "get" it. 
> In a practical sense, null is an unused field -- never held a value and 
> empty is a field that's stored a value, but that value has been deleted.
> 
> Now, someone much smarter than me is going to explain it properly, I hope.
> 
> Susan H.
> 
> 
> 
> 
> > This is a concept that was way beyond me, going back as far as it goes. 
> > Over
> > here we have the "never allow NULLs" camp. Over there we have the "Allow
> > empty fields" camp. I do not comprehend the purpose of the latter. Perhaps
> > some astute lister could educate me on this matter.
>