IsNull，IsEmpty，= Empty和空字符串（即“”）之间有什么区别？为什么我要使用变体 [英] What is the different between IsNull, IsEmpty, =Empty, and an empty string ie "" and why might I use variants

问题描述

我试图了解什么是Null以及什么是Empty变量。他们是一样的吗？空字符串如何放入？

创建MS Access表时，为什么字段具有允许零长度字符串选项？

我一直在努力将Access数据库中的数据加载到变量中（例如，使用DAO或ADO），我发现自己必须声明所有使用Variant的变量。对我来说这似乎是错的。

没有人有任何很好的示例代码来演示这些代码之间的区别，您能解释为什么我会使用一个变体。

任何人都可以建议。

（我发布了自己的答案，提供了许多简单的示例代码，这些代码对我有帮助。它显示了许多函数如何与变量一起使用，希望遇到类似问题的人能够找到

解决方案

变量是唯一可以存储空值或Null值的变量类型，并声明为变量因此：

  Dim aVar为变量
 Dim aVar2'如果没有给出类型，则将其声明为变量
  

在声明变量后，变量立即不存储任何值，并且为空。
也可以使用 aVar = Empty 将空值分配给变量，它将再次为空。

当变量存储为空时，这两个都是正确的：

  aVar =空
 IsEmpty（aVar）
  

您还可以将Variant变量的值设置为是空

  aVar =空
  

现在将是错误的

  aVar =空
 IsEmpty（aVar）
  

但是， IsNull（aVar）是正确的。

当您使用VBA变量存储来自数据库表的数据时，空值特别有用，该数据库表允许将NULL值存储在其字段中。在这种情况下，通常建议所有变量都需要容纳NULL。因此它们都需要是变体，因为这是唯一存储NULL的数据类型。

这很不幸，因为使用更强类型的变量会更好。

一个变体存储Null与变体Empty不同。 Null表示已将值分配给变量，并且该值为Null。

这令人困惑，因为数据库使用Null来指示字段中未存储任何值，并且大多数数据库允许具有任何数据类型的字段为Null。当数据库字段存储Null时，它相当于刚刚被声明为某个值的容器且尚未获得值的VBA变量。就像表字段一样，该变量是一个没有任何内容的容器。

但是，如果从数据库表中给VBA变量赋予了Null值，则它将存储它为Null的事实，因为它不同于从未拥有的Null。

还请注意，存储空字符串的变体与为空不同。 / p>

即不等于空（并且也不等于Null！）

使用时MS Access表存储文本，我建议不要将允许零长度字段属性设置为true，这是默认设置，因为这意味着您的数据库字段也将能够存储（即空字符串）作为Null值。然后，您编写的任何代码都必须使用该字段将存储或Null的可能性。

（很少需要在数据库表中存储空字符串）。

另一种有用的技术是使用 MyString = Nz（MyStringDatabaseField）将任何null转换为空字符串。至少您的代码仅需要测试空字符串，而无需测试Null。此技术还将简化使用存储空字符串的访问表的代码。有时候使用`MyInteger = Nz（MyIntegerDatabaseField）将所有的null转换为0可能是适当的，但是我对此非常不舒服，因为0的含义比空字符串更有意义，并且实际上是Null<> 0！

请注意，在表之间使用OUTER JOIN的SQL语句可能导致返回的记录集在定义基础表字段的字段中包含NULL值，以防止存储NULL。

请注意，如果不使用变量，则数据类型可能会意外使用默认值。例如，

  Dim aInt As Integer 
 aInt =空
 Debug.Print aInt，这将打印0 ，因为0是整数变量的默认值 
  

下面的代码帮助我理解了之间的区别可以用来检查变量的各种函数

  Sub ExperimentsWithVariants（）
 
 Dim aInt As Integer 
 aInt =空
 Debug.Print aInt，这将打印0，因为0是整数变量的默认值 
 
 Dim avar As Variant'结果显示为当aVar被声明为变量
 
 
 Debug.Print -----------------------  
 Debug.Print未设置： 
 Debug.Print ----------------------- 
调试.Print TypeName（avar），TypeName（avar）'空
 Debug.Print aVar =空，（avar =空）'True 
 Debug.Print aVar，avar' ''即空白
 Debug.Print IsNull（aVar），（IsNull（avar））'假
 Debug.Print IsEmpty（aVar），（IsEmpty（avar））'真实
 Debug.Print aVar =，（avar =）'真
 Debug.Print aVar = 0，（avar = 0）'真
 
 
如果avar =空，则
 Debug.Print 
 Debug.Print avar =空，因此如果您将avar =显式设置为空，则上述内容相同 
 Debug。打印 
其他
 avar =空
 Debug.Print 
 Debug.Print ---------------- ------- 
 Debug.Print设置为空 
 Debug.Print ---------------------- - 
 Debug.Print TypeName（avar），TypeName（avar）'空
 Debug.Print aVar =空，（avar =空）'真
 Debug.Print aVar，avar'即空白
 Debug.Print IsNull（aVar），（IsNull（avar））'Fals e 
 Debug.Print IsEmpty（aVar），（IsEmpty（avar））'真
 Debug.Print aVar =，（avar =）'真
 Debug.Print aVar = 0，（avar = 0）'真
如果
 
 avar =空
 Debug.Print 
 Debug.Print  ----------------------- 
 Debug.Print设置为空 
 Debug.Print ----- ------------------ 
 Debug.Print TypeName（avar），TypeName（avar）'空
 Debug.Print aVar =空，（avar =空）'Null 
 Debug.Print aVar，avar'Null 
 Debug.Print IsNull（aVar），（IsNull（avar））'真
 Debug .Print IsEmpty（aVar），（IsEmpty（avar））'False 
 Debug.Print aVar =，（avar =）'空
 Debug.Print aVar = 0，（avar = 0）'空
 
 
 avar = 
 Debug.Print 
 Debug.Print ------ ----------------- 
 Debug.Print设置为EMPT Y STRING，即 
 Debug.Print ----------------------- 
 Debug.Print TypeName（ avar），TypeName（avar）'
 Debug.Print aVar =空，（avar =空）'True 
 Debug.Print aVar，avar'即空白
 Debug.Print IsNull（aVar），（IsNull（avar））'False 
 Debug.Print IsEmpty（aVar），（IsEmpty（avar））'False 
 Debug.Print aVar = ，（avar =）'True 
 Debug.Print aVar = 0，（avar = 0）'字符串
'注意
'为空则返回false，而=返回NULL 
 
 
 avar = 1.23 
 Debug.Print --------------------- - 
 Debug.Print设置为1.23： 
 Debug.Print ----------------------- 
 Debug.Print TypeName（avar），TypeName（avar）'双重
 Debug.Print aVar =空，（avar =空）'真
 Debug.Print aVar，avar '''即空白
 Debug.Print IsNull（aVar），（IsNull（avar））'False 
 Debug.Print IsEmpty（aVar），（IsEmpty（avar））'真
 Debug.Print aVar = ，（avar =）'真实
 Debug.Print aVar = 0，（avar = 0）'真实
 
 
'对于IsEmpty和空字符串（即）和null值，分别为：
 
'IIf（Len（avar& vbNullString）
 
 Debug.Print ----------------------- 
 Debug.Print使用IIf（Len （avar& vbNullString） 
 Debug.Print ----------------------- 
 avar = 
 Debug.Print =，IIf（Len（avar& vbNullString）= 0， Null，IsEmpty或空字符串， NOT）
 avar = 1 
 Debug.Print 1 =，IIf（Len（avar& vbNullString）= 0， Null IsEmpty，或Empty String， NOT）
 avar = Null 
 Debug.Print Null =，IIf（Len（avar& vbNullString）= 0， Null，IsEmpty或空字符串， NOT）
 avar =空
 Debug.Print Empty =，IIf（Len （avar& vbNullString）= 0，空字符串或空字符串，不）
 
 
 
 Debug.Print ---------- ------------- 
 Debug.Print使用TypeName 
 Debug.Print ----------------- ------ 
 Dim dbl1作为Double 
 Debug.Print TypeName（dbl1），TypeName（dbl1）'Double 
 Dim int1作为整数
 Debug.Print  TypeName（int1）， TypeName（int1）'整数
 Dim str1作为字符串
 Debug.Print TypeName（str1），TypeName（str1）'字符串
 
结束子
 
 
 Sub ExperimentsWithNz（）
 
 Debug.Print 
 Debug.Print ------------------- -------------------------------------------------- - 
 Debug.Print ---------------------------------------- ------------------------------ 
 Debug.Print 1a Nz（Null）= = ，Nz（Null）= 
 Debug.Print 1b IsNull（Nz（Null））=，IsNull（Nz（Null））'False 
 
 
调试。打印 ----------------------------------------------- ----------------------- 
 Dim aVar作为变量
 
 Debug.Print 2a Nz（aVar）未分配=，Nz（aVar）'空
 
 aVar =空
 Debug.Print 2b Nz（aVar）空=，Nz（aVar）'空
 
 aVar = Null 
 Debug.Print 2c Nz（aVar）Null =，Nz（aVar）'Null 
 Debug.Print 2d IsNull（Nz（a Var））Null =，（IsNull（Nz（aVar）））'Null 
 
 aVar = 
 Debug.Print 2e Nz（aVar） =， Nz（aVar）''，即空字符串
 
 
 
 Debug.Print -------------------- --------------------------------------------------  
 Dim str1 As String 
 
 Debug.Print 3a Nz（str1）未分配=，Nz（str1）'0 
 str1 =空
 Debug.Print  3b Nz（str1）空=，Nz（str1）'0 
 
 
 Debug.Print ------------------ -------------------------------------------------- - 
 Dim int1 As Integer 
 
 Debug.Print 4a Nz（int1）未分配=，Nz（int1）'0 
 
 int1 =空
 Debug.Print 5b Nz（int1）空=，Nz（int1）'0 
 
'下面的行不能作为字符串运行，因此无法赋值Null 
'str1 =空
 
结束子
 
 
子子交易WithEmptyStringsInADatabaseTable（）
 
 Dim aVar As Varian t 
 
 
 Debug.Print未声明：，Nz（aVar，1）
 
 aVar =空
 Debug.Print aVar = Empty ，Nz（aVar，1）
 
 aVar =空
 Debug.Print aVar = Null，Nz（aVar，1）
 
 aVar = 
 Debug.Print aVar =，Nz（aVar，1）
 
 Debug.Print ----------------- -------------------------------------- 
 Debug.Print处理为空数据库表中的字符串 
 aVar = 
 Debug.Print IIf（aVar =，1，0），IIf（aVar =，1，0）
 
 Debug.Print 
 Debug.Print 
 Debug.Print ------------------------ ------------------------------- 
 Debug.Print处理的表字段可以为Null或一个空字符串 
 Debug.Print导致比仅存储NULL更复杂的代码。 
 Debug.Print 
 Debug.Print下面的代码说明了为什么要设置；允许访问表的零长度属性设置为false 
 Debug.Print 
 
 aVar = Null 
 Debug.Print 1 Null：IIf（Nz（aVar& ，）=，1，0），IIf（aVar& =，1，0）
 aVar = 
调试.Print 2空字符串：IIf（Nz（aVar&，）=，1，0），IIf（aVar& =，1，0 ）
 
 Debug.Print 
 Debug.Print上面的第1行和第2行都可以。 
 Debug.Print 
 Debug.Print 
 
 aVar =空
 Debug.Print 3 Null：Nz（aVar，1），Nz（aVar，1）
 aVar = 
 Debug.Print  4空字符串：Nz（aVar，1），Nz（aVar，1）
 
 Debug.Print 
 Debug.Print但是，第4行不适用于空字符串。 
 Debug.Print 3& 4比1& 2，但如果您的字段可以存储和Null 
 Debug.Print，则必须使用1& 2.作为3& amp;可耻的是4更简单。 
 Debug.Print访问此数据的查询和代码可能会变得混乱 
 
 
 
 End Sub 
  

I'm trying to understand what a Null is and also what an Empty variable is. Are they the same? How do empty strings fit in?

When creating MS Access tables why do fields have an option of "Allow Zero Length String"?

I've been struggling with loading data from an Access database into variables (eg using DAO or ADO) and I find myself having to declare all the variables I use a Variant. This seems so wrong to me.

Does anyone have any good example code that demonstrates how these differ and can you explain why I might use a variant.

Can anyone advise.

(I have posted my own answer with lots of simple example code that has helped me. It shows how lots of functions work with variants, I hope people with similar difficulties find this useful)

解决方案

A variant is the only type of variable that can store an empty value or a Null value, and is declared thus:

Dim aVar as Variant
Dim aVar2    '  if no type is given then it's declared as a variant

Immediately after declaration a variant stores no value and it is empty. Also you can assign empty to a variant using aVar = Empty and it will be empty again.

When a variant stores empty these are both true:

aVar = Empty   
IsEmpty(aVar) 

You can also set the value of a Variant variable to be Null

aVar = Null

These would now be false

aVar = Empty   
IsEmpty(aVar) 

However, IsNull(aVar) would be true.

Null is particularly useful when you use VBA variables to store data that has come from a database table that allows NULL values to be stored in it's fields. In this case it is generally advisable that all the variables need to accommodate storing NULL. So they all need to be variant as this is the only data type that stores NULL.

This is very unfortunate as it would be better to have more strongly typed variables in use.

A variant storing Null is not the same as a variant being Empty. Null indicates that a value was assigned to a variable and the value was Null.

This gets confusing as Null is used by databases to indicate no value has been stored in a field, and most databases allow fields with any datatype to be Null. When a database field stores Null, it is kind of the equivalent to a VBA variant variable having just been declared as "receptacle" for a value and not yet being given a value. The variable, just like the table field is an receptacle without anything in it.

However, if a VBA variant variable is given a Null value from a database table, then it stores the fact that it is Null, as this is different to it never having been given a value and is information your program might want to treat differently.

Also note that a variant storing and empty string "" is not the same as being empty.

ie "" does not equal Empty (and it is not the same as Null either!)

When using MS Access tables to store text, I would advise against setting the "Allow Zero Length" field property to true, which is the default setting, as this means your database field will be able to store "" (ie an empty string) as well as a Null value. Any code you write then has to work with the possibility that the field will store a "" or a Null. It's easier just to work with a Null.

(It's very rare to need to store an empty string in a database table).

Another useful technique is to use MyString = Nz(MyStringDatabaseField) to convert any nulls to be an empty string. At least then your code only has to test for empty strings and not for Nulls as well. This technique will also simplify code working with access tables that store empty strings. Sometimes it may be appropriate to use `MyInteger=Nz(MyIntegerDatabaseField) to convert any nulls to 0, but I am very uncomfortable with this as 0 has a more meaning than an empty string and really Null <> 0!

Note that SQL statements which use an OUTER JOIN between their tables, can result in the returned recordset containing NULL values in fields where the underlying table field is defined to prevent NULLs being stored.

Note that if you do not use a variant the data types then default values may be used unexpectedly. Eg

    Dim aInt As Integer
    aInt = Empty
    Debug.Print aInt, " This will print 0, as 0 is the default value for integer variables"

The code below helped me to understand the difference between the various functions that can be used to inspect variant variables

Sub ExperimentsWithVariants()

    Dim aInt As Integer
    aInt = Empty
    Debug.Print aInt, " This will print 0, as 0 is the default value for integer variables"

    Dim avar As Variant  ' The results shown as comments below were created when aVar was declared as a variant


    Debug.Print "-----------------------"
    Debug.Print "NOT SET:"
    Debug.Print "-----------------------"
    Debug.Print "TypeName(avar)", TypeName(avar)     ' Empty
    Debug.Print "aVar = Empty ", (avar = Empty)      ' True
    Debug.Print "aVar", avar                         '             '' ie blank
    Debug.Print "IsNull(aVar)", (IsNull(avar))       ' False
    Debug.Print "IsEmpty(aVar)", (IsEmpty(avar))     ' True
    Debug.Print "aVar = """"", (avar = "")           ' True
    Debug.Print "aVar = 0", (avar = 0)               ' True


    If avar = Empty Then
        Debug.Print " "
        Debug.Print "avar = Empty so the above would be the same if you set avar = Empty explicitly"
        Debug.Print " """
    Else
        avar = Empty
        Debug.Print " "
        Debug.Print "-----------------------"
        Debug.Print " SET TO Empty"
        Debug.Print "-----------------------"
        Debug.Print "TypeName(avar)", TypeName(avar)     ' Empty
        Debug.Print "aVar = Empty ", (avar = Empty)      ' True
        Debug.Print "aVar", avar                         '            '' ie blank
        Debug.Print "IsNull(aVar)", (IsNull(avar))       ' False
        Debug.Print "IsEmpty(aVar)", (IsEmpty(avar))     ' True
        Debug.Print "aVar = """"", (avar = "")           ' True
        Debug.Print "aVar = 0", (avar = 0)               ' True
    End If

    avar = Null
    Debug.Print " "
    Debug.Print "-----------------------"
    Debug.Print " SET TO NULL"
    Debug.Print "-----------------------"
    Debug.Print "TypeName(avar)", TypeName(avar)     ' Null
    Debug.Print "aVar = Empty ", (avar = Empty)      ' Null
    Debug.Print "aVar", avar                         ' Null
    Debug.Print "IsNull(aVar)", (IsNull(avar))       ' True
    Debug.Print "IsEmpty(aVar)", (IsEmpty(avar))     ' False
    Debug.Print "aVar = """"", (avar = "")           ' Null
    Debug.Print "aVar = 0", (avar = 0)               ' Null


    avar = ""
    Debug.Print " "
    Debug.Print "-----------------------"
    Debug.Print " SET TO EMPTY STRING ie """""
    Debug.Print "-----------------------"
    Debug.Print "TypeName(avar)", TypeName(avar)     '
    Debug.Print "aVar = Empty ", (avar = Empty)      ' True
    Debug.Print "aVar", avar                         '            '' ie blank
    Debug.Print "IsNull(aVar)", (IsNull(avar))       ' False
    Debug.Print "IsEmpty(aVar)", (IsEmpty(avar))     ' False
    Debug.Print "aVar = """"", (avar = "")           ' True
    Debug.Print "aVar = 0", (avar = 0)               ' String
    ' Note
    ' Is empty returns false, whereas ="" returns NULL


    avar = 1.23
    Debug.Print "-----------------------"
    Debug.Print "SET to 1.23:"
    Debug.Print "-----------------------"
    Debug.Print "TypeName(avar)", TypeName(avar)     ' Double
    Debug.Print "aVar = Empty ", (avar = Empty)      ' True
    Debug.Print "aVar", avar                         '             '' ie blank
    Debug.Print "IsNull(aVar)", (IsNull(avar))       ' False
    Debug.Print "IsEmpty(aVar)", (IsEmpty(avar))     ' True
    Debug.Print "aVar = """"", (avar = "")           ' True
    Debug.Print "aVar = 0", (avar = 0)               ' True


    ' You can test for both an IsEmpty AND an empty string (ie "" ) AND a null value with:

    ' IIf(Len(avar & vbNullString)

    Debug.Print "-----------------------"
    Debug.Print "Using IIf(Len(avar & vbNullString) "
    Debug.Print "-----------------------"
    avar = ""
    Debug.Print """""=", IIf(Len(avar & vbNullString) = 0, "Null, IsEmpty, or Empty String", "NOT")
    avar = "1"
    Debug.Print "1 = ", IIf(Len(avar & vbNullString) = 0, "Null IsEmpty,or Empty String", "NOT")
    avar = Null
    Debug.Print "Null = ", IIf(Len(avar & vbNullString) = 0, "Null, IsEmpty or Empty String", "NOT")
    avar = Empty
    Debug.Print "Empty = ", IIf(Len(avar & vbNullString) = 0, "Null or Empty String", "NOT")



    Debug.Print "-----------------------"
    Debug.Print "using TypeName"
    Debug.Print "-----------------------"
    Dim dbl1 As Double
    Debug.Print "TypeName(dbl1) ", TypeName(dbl1)    ' Double
    Dim int1 As Integer
    Debug.Print "TypeName(int1) ", TypeName(int1)    ' Integer
    Dim str1 As String
    Debug.Print "TypeName(str1) ", TypeName(str1)    ' String

End Sub


Sub ExperimentsWithNz()

    Debug.Print " "
    Debug.Print "---------------------------------------------------------------------- "
    Debug.Print "---------------------------------------------------------------------- "
    Debug.Print "1a  Nz(Null)=""""          =", Nz(Null) = ""
    Debug.Print "1b  IsNull(Nz(Null))     =", IsNull(Nz(Null))    ' False


    Debug.Print "---------------------------------------------------------------------- "
    Dim aVar As Variant

    Debug.Print "2a  Nz(aVar) Unassigned  =", Nz(aVar)      ' Null

    aVar = Empty
    Debug.Print "2b  Nz(aVar) Empty       =", Nz(aVar)      ' Null

    aVar = Null
    Debug.Print "2c  Nz(aVar) Null        =", Nz(aVar)      ' Null
    Debug.Print "2d  IsNull(Nz(aVar)) Null=", (IsNull(Nz(aVar)))      ' Null

    aVar = ""
    Debug.Print "2e  Nz(aVar) """"          =", Nz(aVar)      '         ' ie an empty string



    Debug.Print "---------------------------------------------------------------------- "
    Dim str1 As String

    Debug.Print "3a  Nz(str1) Unassigned =", Nz(str1)      ' 0
    str1 = Empty
    Debug.Print "3b  Nz(str1) Empty      =", Nz(str1)      ' 0


    Debug.Print "---------------------------------------------------------------------- "
    Dim int1 As Integer

    Debug.Print "4a Nz(int1) Unassigned  =", Nz(int1)      ' 0

    int1 = Empty
    Debug.Print "5b Nz(int1) Empty       =", Nz(int1)      ' 0

    ' The following line cannot run as a string cannot be assigned Null
    ' str1 = Null

End Sub


Sub DealingWithEmptyStringsInADatabaseTable()

    Dim aVar As Variant


    Debug.Print "UNdeclared: ", Nz(aVar, 1)

    aVar = Empty
    Debug.Print "aVar=Empty ", Nz(aVar, 1)

    aVar = Null
    Debug.Print "aVar=Null ", Nz(aVar, 1)

    aVar = ""
    Debug.Print "aVar="""" ", Nz(aVar, 1)

    Debug.Print " -------------------------------------------------------"
    Debug.Print "Dealing with empty string in a database table"
    aVar = ""
    Debug.Print "IIf(aVar = "", 1, 0) ", IIf(aVar = "", 1, 0)

    Debug.Print " "
    Debug.Print " "
    Debug.Print "-------------------------------------------------------"
    Debug.Print "Dealing with a table field that can have Null or an Empty string"
    Debug.Print "leads to more complex code than if is just stores NULL."
    Debug.Print " "
    Debug.Print "The code below shows WHY you should set the ""; Allow Zero Length "" property of access tables to false"
    Debug.Print " "

    aVar = Null
    Debug.Print "1 Null        : IIf(Nz(aVar & """" ,"""") = """", 1, 0) ", IIf(aVar & "" = "", 1, 0)
    aVar = ""
    Debug.Print "2 Empty String: IIf(Nz(aVar & """" ,"""") = """", 1, 0) ", IIf(aVar & "" = "", 1, 0)

    Debug.Print " "
    Debug.Print "Both lines 1 and 2 above work."
    Debug.Print " "
    Debug.Print " "

    aVar = Null
    Debug.Print "3 Null         : Nz(aVar, 1) ", Nz(aVar, 1)
    aVar = ""
    Debug.Print "4 Empty String:  Nz(aVar, 1) ", Nz(aVar, 1)

    Debug.Print " "
    Debug.Print "however, line 4 does not work for empty string."
    Debug.Print "3 & 4 are much simpler than 1 & 2, but if your field can store """" and Null"
    Debug.Print "you have to use 1 & 2. Which is a shame as 3 & 4 are simpler."
    Debug.Print "Queries and code accessing this data can get messy"



End Sub

这篇关于IsNull，IsEmpty，= Empty和空字符串（即“”）之间有什么区别？为什么我要使用变体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！