Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Loop through table and update date field with increments 1

Status
Not open for further replies.

leicklda

Technical User
Mar 18, 2002
46
US
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!!!
 
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
 
Thanks gol4! Just had to add a rs.edit and rs.update and it worked great.

Much appreciated!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top