Access97 Assistance

[Guest]

I am using Access97. I have one data base which has 1 Table.
The table has 7 fields one of which is DATE
There are 33943 records in this table.
The dates run from 9/25/1995 to the present.

How can I determine if ALL dates are included in this Table. It there a
query that will find any missing dates? Can you help me construct such a
query?

 

[Guest]

The best way to handle this is to first create a Calendar table. First paste
the following function into any standard module in your database:

''''''code begins''''''
Public Function MakeCalendar (strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long

Set dbs = CurrentDb

' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tdf = dbs.TableDefs(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
dbs.Execute strSQL
Else
Exit Function
End If
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strTable & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
dbs.Execute strSQL

' refresh database window
Application.RefreshDatabaseWindow

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"

dbs.Execute strSQL
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
dbs.Execute strSQL
End If
Next varDay
Next dtmDate
End If

End Function
''''''code ends''''''

The call it from the debug window (aka the immediate pane) with:

MakeCalendar "Calendar",#09/25/1995#,Date(),0

You can then use the created table in a query to return all dates not
present in your original table:

SELECT calDate
FROM Calendar LEFT JOIN YourTable
ON Calendar.calDate = YourTable.[Date]
WHERE YourTable.[Date] IS NULL;

BTW I'd avoid using Date as a field name. It could be confused with the
built in Date() function (as used when calling the MakeCalendar function
above) and give incorrect results in some situations. Field names such as
TransactionDate, EventDate etc are better.

Ken Sheridan
Stafford, England

 

[fredg]

I am using Access97. I have one data base which has 1 Table.
The table has 7 fields one of which is DATE
There are 33943 records in this table.
The dates run from 9/25/1995 to the present.

How can I determine if ALL dates are included in this Table. It there a
query that will find any missing dates? Can you help me construct such a
query?

Create a new table named
tblMissingDates
Add one field:
MissedDate Date/Time datatype
Format this field as Short Date.

Copy and paste the below code into a new module (watch for word wrap
on the longer lines):

Sub MissingDates()
Dim DteDate As Date
DteDate = #9/25/1995#

Do While DteDate <= Date

If DCount("[DateField]", "YourTableName", "[DateField] = #" & DteDate
& "#") > 0 Then
Else
CurrentDb.Execute "Insert into tblMissingDates(MissedDate)
Values(#" & DteDate & "#);", dbFailOnError
End If
DteDate = DteDate + 1

Loop
End Sub

This table will contain the missing dates.

*** NOTE ***
Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[John Vinson]

I am using Access97. I have one data base which has 1 Table.
The table has 7 fields one of which is DATE
There are 33943 records in this table.
The dates run from 9/25/1995 to the present.

How can I determine if ALL dates are included in this Table. It there a
query that will find any missing dates? Can you help me construct such a
query?

SELECT yourtable.[DATE]+1
FROM yourtable
LEFT JOIN yourtable AS X
ON X.[DATE] = [yourtable].[DATE] + 1
WHERE X.DATE IS NULL;


If there are no missing dates, this will return the day after the last
actual record. If there are, this query will show the earliest date in
each contiguous block of dates.

This does assume that these are pure dates with no time portion.

John W. Vinson[MVP]