http://msdn.microsoft.com/library/de...HV05186465.asp
"If the value of the variant argument is Null, the Nz function returns
the number zero or a zero-length string (always returns a zero-length
string when used in a query expression)"
****
How many records are there in FirstTable in which Product Is Null.
SELECT COUNT(*) AS CountofNullProdcut
FROM FirstTable
WHERE Product Is Null
CountofNullProduct = 5
There are five records in FirstTable in which Product is Null.
*****
Variants can be Null, but strings cannot.
Since NZ returns a String it cannot return a Null.
SELECT Count(*) AS CountZLS
FROM [SELECT NZ(Product,Null) AS NZProduct
FROM FirstTable]. AS SubQuery
WHERE SubQuery.NZProduct=""
But CountZLS = 0
NZ(Product,Null) returns a Null.
If it returned a string it would return (we assume) a zero length
string.
But there are no zero length strings in
SELECT NZ(Product,Null) AS NZProduct
FROM FirstTable.
(and none that have a null like string such a "Null" either.)
A Null cannot be a string.
Therefore
NZ ****does not**** always returns a zero-length string when used in a
query expression.
************
I believe that this is more accurate description.
Nz, as noted in the object browser, returns a Variant.
There is no Variant JET Data Type.
JET decides to do Something with the Variant. If it has no strong clues
it does what it always does with data (witness its truncation of memo
fields to 255 character text fields), it treats the data as text.
Say the first (row 1) NZ value (variant) is 2. Why not treat it as
byte? Integer? Long? I suppose the answer is that String is safest.
It's likely to have room for whatever may be returned in row 7869.
However it does not do this until it must. If it creates a new table
there is no Variant field type, so it must make a choice, And it
chooses Text. But a calculated field in a query result, examined in
VBA, will be of type Variant.
************
I think it's better to use explcit type conversion whenever one can.
That practice may make this discussion irrelevant.
