IsNull（） - 我无法让它正常工作.. [英] IsNull() - I can't get it to work properly..

问题描述

我正在尝试测试一个记录集，看看SQL查询是否返回了一个

Null值。但是，IsNull（）测试总是返回''False''

响应。我甚至尝试过：


Dim NullCheck As String

NullCheck =""

NullCheck = IsNull（NullCheck）


当我停止代码的一半时，她仍在注册

False.....我认为它是基本的东西，但是它让我击败了。


干杯


Reg

I''m trying to test a recordset to see if the SQL query is returning a
Null value. However, the IsNull() test always returns a ''False''
response. I even tried:

Dim NullCheck As String
NullCheck = ""
NullCheck = IsNull(NullCheck)

When I halted the code half way through, she was still registering
"False"..... I figure it''s something basic, but its got me beat.

Cheers

Reg

推荐答案

Null与零长度字符串不同。


有关解释，请参阅：

Null的常见错误

at：
http://allenbrowne.com /casu-12.html


-

Allen Browne - 微软MVP。西澳大利亚州珀斯。

访问用户提示 - http：// allenbrowne.com/tips.html

回复群组，而不是mvps dot org的allenbrowne。


" Regnab" < P ******* @ gmail.com>在消息中写道

news：11 ********************** @ f14g2000cwb.googlegr oups.com ...

Null is not the same thing as a zero-length string.

For an explanation, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Regnab" <p.*******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

我正在尝试测试一个记录集，看看SQL查询是否返回了一个空值。但是，IsNull（）测试总是返回一个''False''
响应。我甚至尝试过：

Dim NullCheck As String
NullCheck =""
NullCheck = IsNull（NullCheck）

当我暂停代码的一半时通过，她还在注册
False.....我认为这是基本的东西，但它让我击败了。

干杯

Reg

I''m trying to test a recordset to see if the SQL query is returning a
Null value. However, the IsNull() test always returns a ''False''
response. I even tried:

Dim NullCheck As String
NullCheck = ""
NullCheck = IsNull(NullCheck)

When I halted the code half way through, she was still registering
"False"..... I figure it''s something basic, but its got me beat.

Cheers

Reg

除了Allen指出的，String变量永远不会是空的：

唯一变量类型比接受Null值是Variant。


-

Doug Steele，Microsoft Access MVP
http://I.Am/DougSteele

（请不要发电子邮件！）


" Regnab" < P ******* @ gmail.com>在消息中写道

news：11 ********************** @ f14g2000cwb.googlegr oups.com ...

In addition to what Allen''s pointed out, String variables can never be Null:
the only variable type than accepts Null values is the Variant.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Regnab" <p.*******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

我正在尝试测试一个记录集，看看SQL查询是否返回了一个空值。但是，IsNull（）测试总是返回一个''False''
响应。我甚至尝试过：

Dim NullCheck As String
NullCheck =""
NullCheck = IsNull（NullCheck）

当我暂停代码的一半时通过，她还在注册
False.....我认为这是基本的东西，但它让我击败了。

干杯

Reg

I''m trying to test a recordset to see if the SQL query is returning a
Null value. However, the IsNull() test always returns a ''False''
response. I even tried:

Dim NullCheck As String
NullCheck = ""
NullCheck = IsNull(NullCheck)

When I halted the code half way through, she was still registering
"False"..... I figure it''s something basic, but its got me beat.

Cheers

Reg

零长度字符串和空值可能是问题。

请注意，在SQL中，零长度字符串是相等的到一个空间（或者什么SQL在给定空格时选择使用的
）。

考虑以下查询：

表1有9个字段：


更新表1 SET MyNewField ='''';


SELECT *

FROM Table1

WHERE MyNewField ='''';

（按预期返回9条记录）


SELECT *

FROM Table1

WHERE MyNewField为NULL;

（按预期返回零记录）


SELECT *

来自Ta ble1

WHERE MyNewField ='''';

（返回9条记录 - >不是我的期望）


SELECT *

FROM Table1

WHERE MyNewField = Space（100）;

（返回9条记录 - >再次，不是我期望的）


我希望这很少会引起麻烦，但是

如果我们修改一个字符串在需要设置为no的文本字段中，并且

允许zls为yes，通过按BackSpace键直到所有

字符被删除，该字段中的条目将现在是一个空的

字符串，而不是Null，因为它将出现在新记录中。但它和带有Null条目的

记录将显得相同。


并考虑此查询：

SELECT Table1。* ，tbl2002Transactions。*

FROM Table1 INNER JOIN tbl2002Transactions ON

tbl2002Transactions.fldDescription LIKE''*''& Table1.MyNewField& ''*'';


您的猜测是什么？它会返回表1的记录，其中

MyNewField是空（1），还是会返回它返回

的记录表1其中MyNewField是一个零长度的字符串（2），或两者（3），或

（4）。 （我的猜测是错的）。


当我可以的时候，我将必需设置为no，并允许zls为no。这允许我使用

来处理字符字段，知道我只处理NULL

和REAL字符串。麻烦的是我不知道如何在MS-SQL中执行此操作（允许zls

为no），因此我最终使用了很多NULLIF函数。我已经很好地停止了（在任何严肃的应用程序中）使用绑定表单，所以我可以使用我的更新控制
放入字段的内容，追加查询;我试着非常小心编辑记录集和更新，UpdateBatch

然而，

Zero Length Strings and Nulls can be problems.
Note that in SQL land a zero length string is equal to a space (or
whatever SQL chooses to use when given a space).
Consider the queries below:
Table 1 has 9 fields:

UPDATE Table1 SET MyNewField = '''';

SELECT *
FROM Table1
WHERE MyNewField = '''';
(returns 9 records as expected)

SELECT *
FROM Table1
WHERE MyNewField IS NULL;
(returns zero records as expected)

SELECT *
FROM Table1
WHERE MyNewField = '' '';
(returns 9 records -> NOT what I expect)

SELECT *
FROM Table1
WHERE MyNewField = Space(100);
(returns 9 records -> again, not what I expect)

I expect this seldom causes trouble, but
If we modify a string in a text field with required set to no, and
allow zls to yes, by pressing the BackSpace key until all the
characters are erased, the entry in the field will now be an empty
string, and not a Null, as it would be in a new record. But it and a
record with a Null entry will appear identical.

And consider this query:
SELECT Table1.*, tbl2002Transactions.*
FROM Table1 INNER JOIN tbl2002Transactions ON
tbl2002Transactions.fldDescription LIKE ''*'' & Table1.MyNewField & ''*'';

What''s your guess? Will it return the records for Table 1 where
MyNewField IS NULL (1), or will it return it return the records for
Table 1 where MyNewField is a zero length string (2), or both (3), or
neither (4). (My guess was wrong).

When I can, I set required to no, and allow zls to no. This allows me
to work with character fields knowing that I will deal only with NULLs
and "REAL" strings. Trouble is I don''t know how to do this (allow zls
to no) in MS-SQL, so I end up using a lot of NULLIF functions. I''ve
pretty well stopped (in any serious app) using bound forms, so I can
control what''s put into the fields with my update, append queries; I
try to be VERY careful with Editing Recordsets and Update, UpdateBatch
however,

这篇关于IsNull（） - 我无法让它正常工作..的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！