Loop through table and update date field with increments 1

[leicklda]

I have a table with about 1500 records. Table is made up of repeating chunks of 112 records. Each group of 112 records is a pattern that establishes a class scheduling system. It is made of 14 weeks * 8 class types = 112 records.

tblClassSchedule
ID WeekNum ClassID LocationID StartDate
--------------------------------------------------
1 1 1 1 3/1/04
2 1 2 2 3/1/04
3 1 3 3 3/1/04
...
8 1 8 5 3/1/04
9 2 2 2 3/8/04
...
112 14 8 10 6/7/04
113 1 1 1 6/14/04

In a crosstab it looks like this:

Class1 Class2 Class3 ... Class 8
3/1/04 LocA LocB LocC LocF
3/8/04 LocF LocA LocB LocE
...
6/7/04 LocD LocE LocF LocA


In the actual table, all the start dates are empty except for week1 which I've set to 3/1/04. They are empty because I made the table by PasteAppending the repeating pattern several times, but obviously the dates do not repeat, they are continuous and I need a way to fill them in.

How do I loop through the table and update the StartDate fields to have incrementing date values? How I want it to look is already filled in in the table example above. It should work so that every 9th record starts a new StartDate where the new StartDate = Previous StartDate + 7

Any help is very much appreciated!!!

 

[gol4]

a few loops and date add gunction will take care of that. Example code below. untested so double check

dim weeknum as integer
dim intx as integer
dim rst as dao.recordset
dim dtdate as variant
dim strsql as string
strsql = "select * from table order by ID"
set rst=currentdb.openrecordset(strsql)
dtdate = rst!startdate
do until rst.eof
for intx= 1 to 9 'loop 9 times
rst!startdate=dateadd("d",weeknum,dtdate)'add weeknum to date
rst.movenext
next intx
weeknum =weeknum +7 '7 days later
loop

 

[leicklda]

Thanks gol4! Just had to add a rs.edit and rs.update and it worked great.

Much appreciated!