 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / February 2006Test Date data type when un-initialized
If a table field defined as Date is un-initialized, what is it, Empty, Null or zero length? Though they are normally stored as 64 bit numbers, I see that un-initialized fields are not displayed as zero. Thus, it is not clear the proper way to test the field to determine if it is un-initialized. A Date field that contains no value is Null, so the correct test in code is: IsNull([MyDateField]) or in the context of an SQL statement: WHERE [MyDateField] Is Null Any field with no value is Null. Text fields (including Memo and Hyperlink) can also contain a Zero-Length-String, but in 99.99% of cases this is highly undesirable. Unfortunately, Access 2000 and later suddenly decided to default the field's Allow Zero Length property to Yes (opposite of previous versions), so you now have to explicitly set the property to No *every* time you add a Text field to a table. The terms Empty, Nothing, and Missing to not apply to field values. Empty is the value of an uninitialized Variant in VBA code. Nothing is the value of an uninitialized object in VBA code. Missing is the value of an optional paramter that was not supplied in VBA code.  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. > If a table field defined as Date is un-initialized, > what is it, Empty, Null or zero length? Though > they are normally stored as 64 bit numbers, I > see that un-initialized fields are not displayed as > zero. Thus, it is not clear the proper way to test > the field to determine if it is un-initialized. Can I infer from what you've said that table fields can explicitly be set to null? E.g., Me.MyDate = Null If I Dim a VBA variable as Date, I know I can't set that type of variable to Null without encountering a runtime error. I tried to ferret out these answers from HELP, but there seems to be a lacking of such discussions. Thanks, Bill >A Date field that contains no value is Null, so the correct test in code >is: [quoted text clipped - 22 lines] >> zero. Thus, it is not clear the proper way to test >> the field to determine if it is un-initialized. A table field can be set to Null, regardless of its data type, as long as its Required property isn't set to True. The only variable type that can be set to Null, though, is the Variant. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Can I infer from what you've said that table fields can > explicitly be set to null? E.g., Me.MyDate = Null [quoted text clipped - 35 lines] >>> zero. Thus, it is not clear the proper way to test >>> the field to determine if it is un-initialized. Thanks Doug, I just spent some time at Allen's http://allenbrowne.com/tips.html website and got some of my long-standing confusion cleared up, though your lesson hear about the use of Null on fields requiring values helps even further. I had a thread going yesterday trying to find out how to initialize a new record wherein one of the fields would initially be set to null. I.e., Insert(............, MyDateField) Values(..............., Null) but SQL went nuts when I tried specifiying the value as null. Would it work if I instead coded: Dim NullDate as Variant NullDate = Null Insert(............, MyDateField) Values(..............., NullDate) Thanks again, Bill >A table field can be set to Null, regardless of its data type, as long as >its Required property isn't set to True. [quoted text clipped - 40 lines] >>>> zero. Thus, it is not clear the proper way to test >>>> the field to determine if it is un-initialized. Bill, if you have a field in your table, and you enter a record without putting anything in that field, the blank field is Null. If you enter a value, and then remove it, the value is now Null. You can also set it to null programmatically: exactly the same effect as deleting the data out of the field. As Douglas pointed out you cannot delete the value from the field if its Required property is Yes. Required is the oppositie of Nullable. 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. > Thanks Doug, > I just spent some time at Allen's http://allenbrowne.com/tips.html [quoted text clipped - 59 lines] >>>>> zero. Thus, it is not clear the proper way to test >>>>> the field to determine if it is un-initialized. Got it Allen, thanks for helping to finally clear up the whole concept for me. Bill (PS) I've bookmarked your site > Bill, if you have a field in your table, and you enter a record without > putting anything in that field, the blank field is Null. [quoted text clipped - 70 lines] >>>>>> zero. Thus, it is not clear the proper way to test >>>>>> the field to determine if it is un-initialized. Nope! > Thanks Doug, > I just spent some time at Allen's http://allenbrowne.com/tips.html [quoted text clipped - 59 lines] >>>>> zero. Thus, it is not clear the proper way to test >>>>> the field to determine if it is un-initialized. |
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.