Using an update/append query to populate a table based on a formula

[KyleB]

I haven't even been able to find the right criteria to search for in the forums to get meaningful information, so I'll pose this question, and hopefully someone can answer, or point me to the correct keywords.

I wish to make an update query which would populate a table based on the number of the week of the year, and a start and end date. The problem comes from the fact that I am not sure how to create the formulas. The criteria used to create the table list would come from a form, at least for the week, and start date string, but the week of the year would be an auto-increment. I suspect I may need a UDF to create this, but didn't know if there was a simple method to create it within SQL. If it's a UDF, I'll need direction, as I'm weak on defining functions myself.

For instance, the form in question which begins the operation would have a set of two text boxes, unbound. The first box would usually be 1, but could be any week of the year, the second box would be the date of the beginning sequence. The subsequent generation routine should add 6 days to the beginning date, and continue through the end of the year.

The data in the new table would look like this:

1 01/05/2012 01/11/2012
2 01/12/2012 01/18/2012

Etc...

The final week of the year of course would start in 2012, and end in 2013. The week number might be week 1 or week 23, and the start date could be any day of the year, but the end date would always be 6 days after the start date.

Kyle

 

[TexanInParis]

Kyle-

OK, given a start date and week number, you want to generate the values to the end of the year (week 53), correct? It'll take a bit of code.

Dim db As DAO.Database, rst As DAO.Recordset
Dim intWeek As Integer, datStartDate As Date, intI As Integer

    ' Point to this database
    Set db = CurrentDb
    ' Open a recordset on the table
    Set rst = db.OpenRecordset("MyDateTable", dbOpenDynaset, dbAppendOnly)
    ' Grab the week value off the form
    intWeek = Me.txtWeekStart
    ' Grab the starting date off the form
    datStartDate = Me.txtStartDate
    ' Loop to add records
    For intI = intWeek To 53
        ' Add a new record
        rst.AddNew
        ' Set the week number
        rst!WeekNo = intI
        ' Set the start date
        rst!StartDate = datStartDate
        ' Set the end date
        rst!EndDate = datStartDate + 6
        ' Save the record
        rst.Update
        ' Increment the date value
        datStartDate = datStartDate + 7
    Loop
    ' Close out
    rst.Close
    Set rst = Nothing
    Set db = Nothing