Brian Bailly Bronze Collection
Here's a tool that will tell you if you've got some problem dates while it fixes inappropriately formatted fields.
When you have a database that contains multiple tables and hundreds (or perhaps thousands) of records, the idea of updating existing table date field properties and verifying existing data for suspicious dates seems like a nightmare. Under normal circumstances, you'd have to update the Format and Input Mask properties on all your tables containing date fields, and then create queries to search for suspicious dates between 1900 and 1928. You can do this if you want to, but here's a tool to automate this process for you.
In the accompanying Download file, you'll find the file Year2000.ZIP. Unzip it and you'll find an Access 2.0 database, Year2000.MDB. From that database, import the module basYear2000 and frmYear2000. Then just run frmYear2000 (see Figure 1).
The form frmYear2000 will loop through all the tables in your database and do three things:
•examine date fields for suspicious dates
•create queries to show these dates
•update the properties of the date fields in your tables.
The form will let you select whether to examine all tables or just one table. If you select the one-table option, an input box will appear that will allow you to enter the table name (this is especially useful for tables created by periodic downloads). You can also choose whether to update date field properties automatically. If you select update, the Format and Input Mask properties of any date fields encountered will automatically be updated for long four-digit years. The process of examining (and, potentially, updating) all of your tables can take a few minutes, depending on how many tables you have in your database.
When the examination is complete, a message box will appear if there are any tables containing suspicious records with dates between 1900 and 1928 (see Figure 2). One or more queries will be created to let you view those dates, and they'll be listed in the message box. You can click a button to run the queries now, or open them later. If you choose to run the queries later, just look for the queries whose names have the prefix "qry2000". The last portion of each query name is the corresponding table name.
The queries created by the routine are relatively straightforward. Once a suspicious date is found, a query is created that lists all dates in the 1900-1928 range in the table.
The function that updates the table properties is slightly more complicated, but, fortunately, the properties involved are ones that can be updated after a field is added to a table. If that wasn't the case (as with the Attributes property, for instance), I would have had to add a field to the table, copy all the data to the new field, delete the old field, recreate it with the new properties, and then copy the data back. As you can see, the ConvertProperties routine is considerably simpler:
Sub ConvertProperties (strTdNm As String, _
strFldNm As String)
On Error Resume Next
Dim dbDb As Database
Dim fldFld As Field
Dim prpPrp As Property
Dim intI As Integer
Dim intfFmt As Integer
Dim intfMsk As Integer
Set dbDb = DBEngine.Workspaces(0).Databases(0)
Set fldFld = dbDb.TableDefs("" & _
strTdNm & "").Fields("" & strFldNm & "")
intfFmt = False
intfMsk = False
For intI = 0 To fldFld.Properties.Count - 1
If fldFld.Properties(intI).Name = "Format" Then
fldFld.Properties(intI).Value = "mm/dd/yyyy"
intfFmt = True
fldFld.Properties(intI).Name = "InputMask" Then
fldFld.Properties(intI).Value = "99/99/0000;0;_"
intfMsk = True
If intfFmt = False Then
Set prpPrp = fldFld.CreateProperty("Format", _
If intfMsk = False Then
Set prpPrp = fldFld.CreateProperty("InputMask", _
On Error GoTo 0
As you can see, the routine expects to be passed a table name and a field name. The routine then gets a reference to the database, then the table, and then the field. The routine then loops through the field's Properties collection looking for the Format and Input Mask properties. If it finds them, it changes them to the new, Y2K save values. A flag (infMsk and intfFmt) is set when each property is found.
Since these properties aren't Jet-specific but are supplied by Access, it's entirely possible that they don't exist. Because of that, once all the properties for the field are checked, I check to see that both flags have been set. If the flags haven't been set, I create the missing property and add it to the field.
You should find this utility handy as you start to check your databases for problems and bring your table designs into Y2K compliance. Of course, if you haven't started yet, it might be too late already.
Download YEAR2000.ZIP on this page
Brian Bailly is a programmer analyst for Client Server Development.