Populate a table using a conditional loop 1

[theSizz]

I’m trying to populate a table using VBA.
The table has a field Called DayNum whose data property is number. There is another field in the DB called WkEnds whose data property is date/time.
The DayNum field has a corresponding digit for each day of the year i.e. 1,2,3, up to 366 (this is leap year). I want to write code that will populate the WkEnds field based on the number that is in the DayNum field. In other words for day numbers 1 through 6 the WkEnds field would have 1/6/08 as its value (the week always ends on a Sunday) for each record. For day numbers 7 through 13 the WkEnds field would have 1/14/08 as its value and for day numbers 14 through 20 the WkEnds field would have 1/20/08 as its value and so on until the end of the year.

This is what I’ve got so far.

Public Sub setWkEnds()

Dim rs As DAO.Recordset

Dim startval As Date
startval = #1/6/2008#
Set rs = CurrentDb.OpenRecordset("tbldates")

'Select records to be updated
Set rs = CurrentDb.OpenRecordset _
("SELECT * FROM tblDates ")


    rs.MoveFirst
            
        Do
            rs.Edit
            rs.Fields("WkEnds") = startval 'field to update
            rs.Update
            rs.MoveNext
            
        Loop While rs.Fields("DayNum") > 0 And rs.Fields("DayNum") < 7
     
            Do
            rs.Edit
            rs.Fields("WkEnds") = startval + 7 'field to update
            rs.Update
            rs.MoveNext
            
        Loop While rs.Fields("DayNum") > 6 And rs.Fields("DayNum") < 14
            Do
            rs.Edit
            rs.Fields("WkEnds") = startval + 14 'field to update
            rs.Update
            rs.MoveNext
            
        Loop While rs.Fields("DayNum") > 13 And rs.Fields("DayNum") < 21
            rs.Close
            Set rs = Nothing
       
  
End Sub

This produces the desired results but it seems to me that there would be an easier way to code this rather than repeating the Do.........Loop block 52 times. Can someone point me in the right direction?

Thanks for your time.

 

[Remou]

You could use a query to update or to get the weekend without an additional field. For example:

[tt]UPDATE tblDates
SET tblDates.WkEnds = CDate(#1/1/2008#)+[daynum]-Weekday(CDate(#1/1/2008#)+[daynum])+1[/tt]

 

[theSizz]

Thanks Remou that works. As a side note for the week ending dates to compute properly the formula should read:
CDate(#1/6/2008#)+[daynum]-Weekday(CDate(#1/6/2008#)+[daynum])+1
changing the date from 1/1/08 to 1/6/08 makes everything work fine.
Thanks very much.