在查询表达式中使用时，NZ始终返回零长度字符串？ [英] NZ always returns a zero-length string when used in a query expression?
问题描述
http://msdn.microsoft.com/library/de...HV05186465.asp
"如果variant参数的值为Null，则Nz函数返回
数字零或零长度字符串（在查询表达式中使用时始终返回零长度
）"
****
FirstTable中有多少记录产品为空。
SELECT COUNT（*）AS CountofNullProdcut
来自FirstTable
产品是空的
CountofNullProduct = 5
在FirstTable中有五个记录，其中Product是Null。
*****
变量可以是Null，但是字符串不能。
由于NZ返回一个String，因此无法返回Null。
SELECT Count（*）AS CountZLS
FROM [SELECT NZ（Product，Null） ） AS NZProduct
来自FirstTable的
]。 AS SubQuery
WHERE SubQuery.NZProduct =""
但CountZLS = 0
NZ（产品） ，Null）返回Null。
如果它返回一个字符串，它将返回（我们假设）零长度
字符串。
但是那里没有零长度的字符串
SELECT NZ（Product，Null）AS NZProduct
来自FirstTable。
（并且没有像null一样字符串如此Null要么。）
Null不能是字符串。
因此
NZ *** *在
查询表达式中使用时，****不会始终返回零长度字符串。
********* ***
我相信这是更准确的描述。
Nz，如对象浏览器中所述，返回Variant。
那里没有Variant JET数据类型。
JET决定使用Variant做一些事情。如果它没有强有力的线索
它会执行它始终对数据执行的操作（见证其截断备忘录
字段到255个字符的文本字段），它将数据视为文本。
假设第一个（第1行）NZ值（变体）是2.为什么不将它视为
字节？整数？长？我想答案是字符串是最安全的。
它可能有空间用于第7869行中可能返回的任何内容。
但是它不会这样做直到它必须。如果它创建一个新表
没有Variant字段类型，所以它必须做出选择，并且它b / b
选择Text。但是，在
VBA中检查的查询结果中的计算字段将是Variant类型。
************
我认为最好在任何人都可以使用explcit类型转换。
这种做法可能会使这个讨论变得无关紧要。
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.
推荐答案
实际上，如果您没有指定
返回什么，则零长度字符串将是默认值。但是，在您给出的示例中，您已通过向函数添加第二个参数来指定
要返回的内容。另外，告诉
当值为Null时返回Null是浪费时间。
-
Wayne Morgan
MS Access MVP
" Lyle Fairfield" < LY *********** @ aim.com>在消息中写道
news：11 ********************* @ g49g2000cwa.googlegro ups.com ...
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
"如果variant参数的值为Null，则Nz函数返回
数字零或零长度字符串（在查询表达式中使用时始终返回零长度的字符串）
****
如何FirstTable中有许多记录，其中Product is Null。
SELECT COUNT（*）AS CountofNullProdcut
FROM FirstTable
产品是否为空
CountofNullProduct = 5
在FirstTable中有五个记录，其中Product是Null。
*****
变量可以是Null，但字符串不能。
由于NZ返回一个String，它不能返回Null。
SELECT Count（*）AS CountZL来自[SELECT NZ（产品，零）AS NZProduct
来自FirstTable]。 AS SubQuery
WHERE SubQuery.NZProduct =""
但CountZLS = 0
NZ（Product，Null）返回Null。
如果它返回一个字符串，它将返回（我们假设）一个零长度
字符串。
但是没有零长度字符串
SELECT NZ（Product，Null）AS NZProduct
FROM FirstTable。
（并且没有像字符串一样的空字符，如Null。）
Null不能是字符串。
因此
NZ * ***在
查询表达式中使用***时，****不会始终返回零长度字符串。
************ 我相信这是更准确的描述。
Nz，如对象浏览器中所述，返回Variant。
没有Variant JET数据类型。
JET决定用它做一些事情。变种。如果它没有强有力的线索
它完成它总是对数据做的事情（见证它将备注
字段截断到255个字符文本字段），它将数据视为文本。
说第一个（第1行）NZ值（变量）是2.为什么不将其视为
字节？整数？长？我想答案是字符串是最安全的。
它可能有空间用于第7869行中可能返回的任何内容。
但是它必须这样做才能做到这一点。如果它创建一个新表
没有Variant字段类型，所以它必须做出选择，并且它选择Text。但是在VBA中检查的查询结果中的计算字段将是Variant类型。
************
我认为它更好任何人都可以使用explcit类型转换。
这种做法可能使这个讨论无关紧要。
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.
我同意。无论很明显，这种说法都不正确：
"如果变量参数的值为Null，则Nz函数返回
数字零或零 - 长度字符串（在查询表达式中使用时始终返回零长度
字符串）"
始终表示始终;这并不意味着什么时候我们不浪费时间。
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.
Lyle Fairfield< ly *********** @ aim。 COM>写道：
：我同意。无论很明显这个陈述不是真的：
："如果variant参数的值为Null，则Nz函数返回
：数字为零或者a零长度字符串（在查询表达式中使用时始终返回零长度
：字符串）"
：始终表示始终;这并不意味着我们什么时候不浪费时间。
好的。我知道我没有任何事情进入这个专家之间讨论Talmudic的讨论，但是本周我有了手术隧道
手术，所以我甚至比通常在我可以找到的东西中我很开心。
这是我在
$ b $的相关部分找到的措辞b Access 2003 VBA语言参考Nz函数说明：
---开始Access 2003 VBA语言参考----------------- -------
参数说明
-------- -----------
variant数据类型Variant的变量。
valueifnull可选（除非在查询中使用）。如果variant参数为Null，则为Variant提供要返回的值的Variant。这个
参数使您能够返回零以外的值或
零长度字符串。
注意如果使用使用valueifnull参数在没有
的查询中的表达式中的Nz函数，结果将是包含空值的字段中的零长度
字符串。 />
如果variant参数的值为Null，则Nz函数返回
数字零或零长度字符串（总是返回零长度
$ b在查询表达式中使用$ b字符串），具体取决于
上下文是否指示值应为数字或字符串。如果包含
可选的valueifnull参数，则Nz函数将返回该参数指定的值，如果variant参数为
Null 。当在查询表达式中使用时，NZ函数应始终包含valueifnull参数，
如果variant的值不是Null，那么Nz函数返回变量的
值。
---结束Access 2003 VBA语言参考----------------- -------
因此，引用总是返回的文字引用了
如果你在查询中返回的内容没有指定
不同的东西 - 否则你必须把它带到
意味着即使你指定了它也会返回/>
valueifnull参数中的其他内容，一个参数
'仅在
之后的句子中讨论''总是返回''语句。
...永远并不总是意味着
- 帮助
（跑去隐藏）
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)
这篇关于在查询表达式中使用时，NZ始终返回零长度字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
