 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / July 2005Difference Between Null, Empty and ""
Can anyone explain the difference between these and when to use each, particularly in conditional statements? Null represents the absence of any value (i.e., no record, or no field value)...and can only be assigned to a Variant data type. Empty indicates that a Variant type variable has been declared, but a value has not been assigned....its basically a default assignment. "" represents an empty string. Each indicates no value, but in a different context. Logically, you have to code differently to test for the condition. Or you might use something like this... Function IsNothing(varArg As Variant) As Boolean 'Checks whether argument is Null, Empty, empty string, or Nothing On Error GoTo Err_IsNothing IsNothing = False Select Case VarType(varArg) Case vbEmpty IsNothing = True Case vbNull IsNothing = True Case vbString If Len(varArg) = 0 Then IsNothing = True End If Case vbObject If varArg Is Nothing Then IsNothing = True End If End Select Exit Function Err_IsNothing: IsNothing = True End Function If the data type of the variable being passed is something other than Variant, String, or an Object, there is not much point in using the function. For example, passing any Integer variable would also result in False because an Integer is 0 by default...therefore, it has a value.  SignaturePaul Overway Logico Solutions, LLC www.logico-solutions.com > Can anyone explain the difference between these and when to use each, > particularly in conditional statements? Paul has given you a good answer. I'll add an example. If you have a table of clients with phone numbers. You don't know everyone's numbers, so some fields are null until you find out. Then you learn that one of the students has no phone. The number is not unknown (null); it is known to be non-existent. You could record that in the database as a zero-length string (zls). To the user, there is no visible difference between null and a zls, so trying to create that kind of distinction generally does little more than confuse the users. I strongly suggest that you therefore set the AllowZeroLength property to No for all text fields in your tables, so you don't accidentally end up with a ZLS where you intended a Null. If you allow ZLS, you every query and every piece of code must check for both cases--clumsy, inefficient, and error-prone. For more info, see: Nulls: Do I need them? at: http://allenbrowne.com/casu-11.html SignatureAllen 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. > Null represents the absence of any value (i.e., no record, or no field > value)...and can only be assigned to a Variant data type. Empty indicates [quoted text clipped - 39 lines] >> Can anyone explain the difference between these and when to use each, >> particularly in conditional statements? There is also that once a value is put into a field, that was previously NULL, if you DELETE the value the value in the field because a zero-length string as opposed to NULL. > Paul has given you a good answer. I'll add an example. > [quoted text clipped - 16 lines] > at: > http://allenbrowne.com/casu-11.html Ok, if this all true, do I need to always test if the null status and the "" status of a field to verify that it contains no data? It is a little time consuming to keep doing multiple conditional statements. In other words, for example, if I test for "", is that enough to catch the null and empty values too? > There is also that once a value is put into a field, that was previously > NULL, if you DELETE the value the value in the field because a [quoted text clipped - 20 lines] > > at: > > http://allenbrowne.com/casu-11.html No. Testing for "" (a zero-length) string will not catch values that are NULL. One option would be to create a function that tests for both as in function isValidLength(varValue as Variant) as boolean isValidLength = False if IsNull(varValue) = False AND Length(varValue) > 0 then isValidLength = True end if end function The function was written off the top of my head, so it may need some tweaking. > Ok, if this all true, do I need to always test if the null status and the "" > status of a field to verify that it contains no data? It is a little time [quoted text clipped - 47 lines] >>>at: >>> http://allenbrowne.com/casu-11.html Agreed that this is just wasteful. You can avoid that by setting the AllowZeroLength property to No for all Text fields, Memo fields, and Hyperlink fields throughout your application. Testing for a zls alone (i.e. if it is equal to "") will NOT work. You do not need to test for Empty in a field. That does not apply to fields--only to Variants in VBA code, where you declared a variant but did not assign anything to it. The code below will loop through all fields in all tables of your database and set the AllowZeroLength field to No, listing the fields that were changed. If you already have data in the tables you would also need to run an update query on those fields to find any records that already contain the ZLS. Function FixZLS() Dim db As DAO.Database Dim tdf As DAO.TableDef Dim fld As DAO.Field Dim prp As DAO.Property Const conPropName = "AllowZeroLength" Const conPropValue = False Set db = CurrentDb() For Each tdf In db.TableDefs If (tdf.Attributes And dbSystemObject) = 0 Then For Each fld In tdf.Fields If fld.Properties(conPropName) Then Debug.Print tdf.Name & "." & fld.Name fld.Properties(conPropName) = conPropValue End If Next End If Next End Function SignatureAllen 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. > Ok, if this all true, do I need to always test if the null status and the > "" [quoted text clipped - 35 lines] >> > at: >> > http://allenbrowne.com/casu-11.html > Ok, if this all true, do I need to always test if the null status and > the "" status of a field to verify that it contains no data? It is a > little time consuming to keep doing multiple conditional statements. > In other words, for example, if I test for "", is that enough to > catch the null and empty values too? As Allen says, if you don't allow the field to contain a ZLS, this problemn doesn't arise. If you want to write code that tests whether a field is Null or a ZLS, and you don't care which, you can do it in one simple test, like this: If Len(YourField & vbNullString) = 0 Then ' The field is either Null or a zero-length string End If This works because of a special characteristic of the concatenation operator '&' -- it converts a Null being concatenated into a zero-length string. If you want to include the possibility that the field contains only some number of spaces (not easy to get, in Access), then you can modify the above to If Len(Trim(YourField & vbNullString)) = 0 Then ' The field is either Null, spaces, or a zero-length string End If SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) The value becomes a zero length string only if you allow zero-length strings in the field. Otherwise when you delete the value, null is stored in the field. > There is also that once a value is put into a field, that was previously > NULL, if you DELETE the value the value in the field because a [quoted text clipped - 20 lines] > > at: > > http://allenbrowne.com/casu-11.html |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.