By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,392 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

NZ always returns a zero-length string when used in a query expression?

P: n/a
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.

Dec 23 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Actually, the zero length string would be a default if you don't specify
what to return. However, in the example you've given, you have specified
what to return by adding the second argument to the function. Also, telling
it to return Null when the value is Null is kind of a waste of time.

--
Wayne Morgan
MS Access MVP
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...
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.

Dec 23 '05 #2

P: n/a
I agree. Regardless it is clear that this statement is not true:
"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)"
Always means always; it does not mean when we are not wasting our time.

Dec 23 '05 #3

P: n/a
Lyle Fairfield <ly***********@aim.com> wrote:
: I agree. Regardless it is clear that this statement is not true:
: "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)"
: Always means always; it does not mean when we are not wasting our time.

OK. I know that I have no business getting into this
Talmudic-sounding discussion among experts, but I had carpal tunnel
surgery this week so I'm even more limited than usual in the things I
can find to amuse me.

This is the phrasing I find in the relevant section of the
Access 2003 VBA Language Reference description of the Nz function:

---begin Access 2003 VBA Language Reference------------------------
Argument Description
-------- -----------
variant A variable of data type Variant.

valueifnull Optional (unless used in a query). A Variant that supplies
a value to be returned if the variant argument is Null. This
argument enables you to return a value other than zero or a
zero-length string.

Note If you use the Nz function in an expression in a query without
using the valueifnull argument, the results will be a zero-length
string in the fields that contain null values.
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), depending on whether the
context indicates the value should be a number or a string. If the
optional valueifnull argument is included, then the Nz function will
return the value specified by that argument if the variant argument is
Null. When used in a query expression, the NZ function should always
include the valueifnull argument,

If the value of variant isn't Null, then the Nz function returns the
value of variant.
---end Access 2003 VBA Language Reference------------------------

So that 'always returns' text that you quote refers to
what it will return in a query if you don't specify
something different--otherwise you'd have to take it to
mean that it will return that even if you've specified
something else in the valueifnull argument, an argument
that's only discussed in the sentence following the
'always returns' statement.

...always doesn't always mean always

--thelma
(running to hide)
Dec 23 '05 #4

P: n/a
Thelma Lubkin wrote:
...always doesn't always mean always

--thelma
(running to hide)


Humility in smart people is unbecoming.

Dec 23 '05 #5

P: n/a
Lyle Fairfield <ly***********@aim.com> wrote:
: Thelma Lubkin wrote:

:> ...always doesn't always mean always
:>
:> --thelma
:> (running to hide)

: Humility in smart people is unbecoming.

How about convuluted forms of saying 'you're right'?
Dec 23 '05 #6

P: n/a
Thelma Lubkin wrote:
...always doesn't always mean always

--thelma
(running to hide)


Experience is easy to gain but many experienced developers will never
express their ideas well, will never think clearly. You do.

There is no reason for you to be hiding or lurking. Your ideas are as
good as or better than those of most posters in CDMA.

IMO Jan 1, 2006 would be a good date for you to stop hiding. Perhaps
December 24, 2005 would be just as satisfactory.

--
Lyle Fairfield
Dec 23 '05 #7

P: n/a
Thelma Lubkin wrote:
How about convuluted forms of saying 'you're right'?


Zero-Length String

--
Lyle Fairfield
Dec 23 '05 #8

P: n/a
"Lyle Fairfield" <ly***********@aim.com> wrote in
news:11*********************@g14g2000cwa.googlegro ups.com:
I agree. Regardless it is clear that this statement is not true:
"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)"
Always means always; it does not mean when we are not wasting our
time.


Sorry, but I just don't see your point.

Nz() called without a null-case argument behaves as you said above.

But in this case, a null-case argument was, in fact, supplied, so
your observation above is inapplicable.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Dec 24 '05 #9

P: n/a
"Lyle Fairfield" <ly***********@aim.com> wrote in
news:11********************@g14g2000cwa.googlegrou ps.com:
Thelma Lubkin wrote:
...always doesn't always mean always

--thelma
(running to hide)


Humility in smart people is unbecoming.


The smartest people I've ever known are the ones least convinced of
their own competence.

Lyle, feel free to turn this into an insult directed at me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Dec 24 '05 #10

P: n/a
Naah! You've been civilized and urbane recently. There is no reason to
insult you.

But since you've given me a free shot I'll save it up to spring on you
when you are unsuspecting and vulnerable, if you don't mind.
No, not really.

I'll just enjoy the peace while it lasts.

Dec 24 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.