March 2, 2021
Hot Topics:

VB Coding Tip: Using the Date Type with an ADO Source

  • By John Percival
  • Send Email »
  • More Articles »

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

Set lRs = New ADOR.Recordset
lRs.Fields.Append "MyDate", _
                  adDate, , adFldIsNullable
lRs!MyDate = Date
MsgBox lRs!MyDate
lDate = lRs!MyDate
lDateVar = lRs!MyDate
'lDate = Null 'This would not work
lDateVar = Null 'This will work
lRs!MyDate = lDateVar
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.

This article was originally published on November 19, 2002

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date