Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Access97 Assistance

Discussion in 'Information Technology' started by Richard194, Jul 28, 2006.

  1. Richard194

    Richard194
    Expand Collapse
    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?
     
  2. Loading...

    Similar Threads Forum Date
    Grammer / Vyakarn Request For Assistance With Marking Tests Language, Arts & Culture May 22, 2013
    Translation Assistance Questions and Answers Sep 9, 2012
    UK UK Police Seek Assistance: Terrifying moments - Sikh Protesters Storm Community Centre, Trash It Breaking News Oct 13, 2011
    S Asia International Assistance Mission Massacre: 10 Civilian Volunteers Killed In Afghanistan Breaking News Aug 9, 2010
    Khalsa Aid Canadian Sikhs Support Khalsa Aide in Offering Assistance to Haiti Sikh Organisations Feb 20, 2010

  3. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    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

    "Richard194" wrote:

    > 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?
    >
     
  4. fredg

    fredg
    Expand Collapse
    Guest

    On Tue, 18 Jul 2006 09:45:02 -0700, Richard194 wrote:

    > 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'

    --
    Fred
    Please respond only to this newsgroup.
    I do not reply to personal e-mail
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 18 Jul 2006 09:45:02 -0700, Richard194
    <Richard194@discussions.microsoft.com> wrote:

    >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]
     

Share This Page