View Single Post
 
Old November 19th, 2007, 10:19 AM
mmcdonal mmcdonal is offline
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Here is how I would do this (assuming your ID is autonumber):

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim dtStart As Date
Dim dtEnd As Date
Dim dtWeek As Date
Dim i As Integer 'check for runaway loop

i = 0
dtStart = (input start date here like "01/01/2009")
dtEnd = (input end date here, plus one day, like "01/01/2010")

sSQL = "SELECT * FROM tblYourTableName"
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until dtStart = dtEnd Or i = 400
    If DatePart("w", dtStart) = 1 Then
        dtWeek = dtStart
    End If

    rs.AddNew
        rs("Date") = dtStart
        rs("Week Commencing") = dtWeek
        rs.Update

    dtStart = DateAdd("d", 1, dtStart)
    i = i + 1
Loop

rs.Close

This will add all but the first 6 or less Week Commencing dates unless 2009 starts on a Sunday. You will only have to fill those in by hand.

Also, "Date" is a reserved name, and should not be used for a field name.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com