Fill an empty table with values 1

[stotzc001]

I have a database that works on a fiscal year basis. At the end of each fiscal year I close out the database and transfer any carryover data to a new database for the next fiscal year. One table I have in the database tracks data within the FY on a daily basis. It has 365 records starting from 10/1/2004 to 9/30/2005.

What is want to do with the new fiscal year database is clear out all the old values and fill the table with new records from 10/1/2005 to 9/30/2006.

Any Ideas?

 

[mndrlion]

Here is a function that will do the job:

Sub ResetTblDaily(intYear As Integer)

    Dim rst As DAO.Recordset
    
    Dim strSQL As String
    
    Dim dtStart As Date
    Dim dtEnd As Date
    Dim dt As Date
    
    ' clear the existing data
    strSQL = "DELETE * FROM tblDaily;"
    
    CurrentDb.Execute strSQL


    ' create records for year passed to here
    dtStart = DateSerial(intYear, 10, 1)
    dtEnd = DateSerial(intYear + 1, 9, 30)
    
    Set rst = CurrentDb.OpenRecordset("tblDaily")
    
    dt = dtStart
    Do Until dt > dtEnd
        
        rst.AddNew
        rst![Date] = dt
        rst.Update
        
        dt = dt + 1
    Loop
    
    rst.Close
    Set rst = Nothing
End Sub

 

[stotzc001]

Works like a champ. Enjoy the star & thanks.