IsNull、IsEmpty、=Empty 和空字符串(即“")之间有什么区别?为什么我可以使用变体

本文介绍了IsNull、IsEmpty、=Empty 和空字符串(即“")之间有什么区别?为什么我可以使用变体的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我试图了解 Null 是什么以及 Empty 变量是什么.他们是一样的吗?空字符串如何插入?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? 在创建 MS Access 表时，为什么字段有允许零长度字符串"选项?When creating MS Access tables why do fields have an option of "Allow Zero Length String"? 我一直在努力将数据从 Access 数据库加载到变量中(例如使用 DAO 或 ADO)，我发现自己必须声明我使用 Variant 的所有变量.这对我来说似乎是错误的.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.谁能给点建议.(我已经发布了我自己的答案，其中包含许多对我有帮助的简单示例代码.它展示了许多函数如何与变体一起工作，我希望有类似困难的人发现这很有用)(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)推荐答案variant 是唯一可以存储空值或 Null 值的变量类型，因此声明: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 VariantDim aVar2 ' if no type is given then it's declared as a variant在声明后立即变体不存储任何值并且它是空的.您也可以使用 aVar = Empty 将空分配给变体，它会再次为空.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) 您还可以将 Variant 变量的值设置为 Null You can also set the value of a Variant variable to be Null aVar = Null这些现在都是假的aVar = Empty IsEmpty(aVar) 然而，IsNull(aVar) 是正确的.当您使用 VBA 变量存储来自允许将 NULL 值存储在其字段中的数据库表的数据时，Null 尤其有用.在这种情况下，通常建议所有变量都需要适应存储 NULL.所以它们都需要变体，因为这是唯一存储 NULL 的数据类型.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.存储 Null 的变体与为 Empty 的变体不同.Null 表示已将值分配给变量并且该值为 Null.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. 这会让人感到困惑，因为数据库使用 Null 来表示字段中没有存储任何值，并且大多数数据库允许具有任何数据类型的字段为 Null.当一个数据库字段存储 Null 时，它相当于一个 VBA 变体变量，它刚刚被声明为一个值的接受器"并且还没有被赋予一个值.变量，就像表字段一样，是一个没有任何内容的容器.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. 但是，如果从数据库表中为 VBA 变体变量赋予 Null 值，那么它会存储它为 Null 的事实，因为这与从未赋予过值不同，并且是您的程序可能想要的信息区别对待.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.即 "" 不等于 Empty(它也不等于 Null！)ie "" does not equal Empty (and it is not the same as Null either!)当使用 MS Access 表存储文本时，我建议不要将允许零长度"字段属性设置为 true，这是默认设置，因为这意味着您的数据库字段将能够存储"(即空字符串)以及 Null 值.您编写的任何代码都必须处理该字段将存储 "" 或 Null 的可能性.使用 Null 更容易.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).另一个有用的技术是使用 MyString = Nz(MyStringDatabaseField) 将任何空值转换为空字符串.至少你的代码只需要测试空字符串而不是空字符串.此技术还将简化使用存储空字符串的访问表的代码.有时使用 `MyInteger=Nz(MyIntegerDatabaseField) 将任何空值转换为 0 可能是合适的，但我对此感到非常不舒服，因为 0 比空字符串更有意义，而且真的是 Null 0！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!请注意，在表之间使用 OUTER JOIN 的 SQL 语句可能导致返回的记录集在字段中包含 NULL 值，其中定义了基础表字段以防止存储 NULL.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 variablesSub 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) ' StringEnd SubSub 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 = NullEnd SubSub 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 和空字符串(即“")之间有什么区别?为什么我可以使用变体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！