I want to write a pogram to go through each table, loop through all fields, choose the date/time fields and test any date values where the year < 1000 or > 2100.
So i need to test if a field is a date field, how do i do this?
I also need to test a field for a PK so i can save that too.
Looping through each table is straight forward enough, its the other two problems im after.
Date/Time data types
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Date/Time data types
Are the tables stored in the Access database or in SQL Server?
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Date/Time data types
You can use DAO:
Code: Select all
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Not tdf.Name Like "MSys*" And Not tdf.Name Like "~*" Then
For Each fld In tdf.Fields
If fld.Type = dbDate Then
Debug.Print tdf.Name, fld.Name
End If
Next fld
End If
Next tdf
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Date/Time data types
And here is code to list the primary key fields. Note that a primary key may consist of more than one field.
Code: Select all
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Not tdf.Name Like "MSys*" And Not tdf.Name Like "~*" Then
For Each idx In tdf.Indexes
If idx.Primary Then
For Each fld In idx.Fields
Debug.Print tdf.Name, idx.Name, fld.Name
Next fld
End If
Next idx
End If
Next tdf
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Date/Time data types
Thank you Hans, your blood is worth bottling.