by Fred Thorlin
The year 2000 problem revolves around the ambiguity of dates in which the year is entered with only two digits. Does 8/12/40 refer to August 12, 1940 or 2040? If I add 50 years to September 14, 1990 will the result be looked at as September 14, 2040 or 1 940 since, in the mm/dd/yy format, those two look identical. This is nominally a non-problem in Visual Basic because it uses a date/time variable in the programming language and the related databases.
This is actually a double precision floating point number where the integral part holds the number of days since December 30, 1899. Since negative values are allowed dates can range from January 1, 100, to December 31, 9999. The fractional part provides the time of day with .5 being noon. If you keep all dates and times in date/time fields, the year 2000 problem can’t affect the computational elements of your program. (Alas, Microsoft breaks this rule in biblio.mdb, a sample database included Visual Basic.)
In spite of this excellent support, there are still possibilities of problem dates entering your applications. They can occur in the areas of input, maintenance and output. Input problems result from accepting year information in the form of 2-digit stri ngs. If you do this your program will probably prefix those digits with a “19”. When users start entering “00” and “01” they will get some surprising results. The best defense is to not accept dates this way. Instead, have them enter 4-digit years, or pi ck dates from a list. Best is to let them pick dates from a displayed calendar which can be date range controlled such as with Sheridan’s Data Widgets. Output problems result from displaying 2-digit years when there can be some ambiguity as to their mean ing. A rare problem.
The maintenance problem is the worst of the bunch. This includes such things as using imported data and operating in environments where users can access the database directly, such as via Access, rather than via the Visual Basic application. All manner o f things can and do happen. The quality of your programming is unlikely to help here. Most of these problems can be detected by just looking at the date values and seeing if they are in a reasonable range, i.e. errors tend to be large, on the order of 10 0 years or more. The problem then becomes inspecting the millions of values in your databases. VB5 has some elegant and powerful tools that make this task easy.
We want to locate the extreme date values in a database. If these are reasonable we can be reasonably confident that we aren’t victims of the year 2000 problem. We might even catch some simple data entry errors along the way. The technique described here is applicable to any database accessible via the data access object. It identifies the extremities of the range of date values in the indicated database. The method is to locate every date field in the database and display the minimum and maximum date v alues contained therein.
The Form_Activate event begins by opening the database. It then cycles through the members of the database’s tables collection using the For Each construct. This powerful statement allows us to reference each table within the database’s collection of tables without knowing the name of any of them. We then use the same mechanism to look at each member of the table’s Fields collection in a nest ed For Each loop. Each field’s Type property is tested to see if it is a date/time type of field. If it is, the minimum and maximum are extracted by the GetMinMax subroutine.
Add some code to format the results a little better. Then add some more code to display the extreme values for tables and the entire database. Polish it off with a Common Dialog Control to select the database. Now you have a handy auditing tool in just a few statements. By using the For Each and the database collections we have been able to examine a database with no knowledge of its internal structure.
Dim db As Database ‘ Database examined
Dim TableX As Object ‘ Table being examined
Dim FieldX As Object ‘ Field being examined
Dim XMax As Date ‘ Max date in Field
Dim XMin As Date ‘ Min date in field
Const dbName = “C:\Program Files\DevStudio\VB\Nwind.mdb”
Private Sub Form_Activate()
Set db = OpenDatabase(dbName) ‘ Open database
For Each TableX In db.TableDefs ‘ For each table in database
For Each FieldX In TableX.Fields ‘ For each field in table
Select Case FieldX.Type ‘ Examine each field
Case dbTime, dbTimeStamp, dbDate ‘ Fields containing dates
GetMaxMin ‘ Get Min and Max for table
Print TableX.Name, FieldX.Name, XMin, XMax ‘ Display results
End Select
Next
Next
End Sub
Public Sub GetMaxMin() ‘ Determines min and max date in current field
Dim SQL As String
Dim rsExtreme As Recordset
SQL = “SELECT DISTINCTROW Max([“ & TableX.Name & “].[“ & FieldX.Name
SQL = SQL & “]) AS [MaxOf” & FieldX.Name
SQL = SQL & “] FROM [“ & TableX.Name & “];”
Set rsExtreme = db.OpenRecordset(SQL)
If Not IsNull(rsExtreme(0)) Then
XMax = rsExtreme(0)
End If
SQL = “SELECT DISTINCTROW Min([“ & TableX.Name & “].[“ & FieldX.Name
SQL = SQL & “]) AS [MinOf” & FieldX.Name
SQL = SQL & “] FROM [“ & TableX.Name & “];”
Set rsExtreme = db.OpenRecordset(SQL)
If Not IsNull(rsExtreme(0)) Then
XMin = rsExtreme(0)
End If
rsExtreme.close
End Sub
Fred Thorlin is a HAL-PC member and president of Personal Instruments, Inc. The Houston, Texas based company does Visual Basic development and consulting. You may contact him at fredt@hal-pc.org. C 1998 Personal Instruments, Inc. All rights reserved.
E-mail me at webmaster@hal-pc.org with any comments you have and tell me what you want to see here.