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

Monday Nov 18th 2002 by John Percival

Date stamp your databases with this code from John Percival.

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.

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved