VB Coding Tip: Using the Date Type with an ADO Source
The first thought to handle a date in VB is to use a Date type variable. In fact, this will not work for Null dates coming from an ADO source. The reason is that the date internal type has a different behavior than the adDate ADO type. To see the differences between Date and adDate take a look at the following code (under VB6 with Microsoft ActiveX Data Objects Recordset 2.0 Library, but the same would happen with Microsoft ActiveX Data Objects 2.0 Library):
Private Sub Date_handling() Dim lDate As Date Dim lDateVar As Variant Dim lRs As ADOR.Recordset 'Initialization: Set lRs = New ADOR.Recordset lRs.Fields.Append "MyDate", _ adDate, , adFldIsNullable lRs.Open lRs.AddNew lRs!MyDate = Date MsgBox lRs!MyDate 'Storing: lDate = lRs!MyDate lDateVar = lRs!MyDate 'Setting: 'lDate = Null 'This would not work lDateVar = Null 'This will work lRs!MyDate = lDateVar 'lRs!Update lRs.Close End Sub
A String is not more appropriate: a Null string does not represent a Null date. Unfortunately, the default setting in VB6's DataEnvironment puts a TextBox on the form when you drag and drop a date field. To have a good internal representation of the date, you should use a Variant.