Need DB to generate weekly dates from a start and end date

[Tommo]

Hi.



I am trying to create a simple database for my partners Dance school.

I have tables for my classes and term dates.

I want to be able to create registers that will give me a box for each class.

ie; If the class is on say a Wednesday, the term starts on 02/09/2015 and ends on 1/1/2016 I want to work out the date of every Wednesday between.

Cheers

[ranman256]

these tables:

tClasses
----------
ClassID (autonum)
ClassName
DateStart
DateEnd
Instructor

tWorkouts
-------------------
WorkID (auto)
ClassID (long)
ClassDate


tClassRoster
---------------
RosterID (autonum)
ClassID (long) FK from tClasses
NameID

tNames
--------------
NameID
FirstName
LastNAme
Address


then in a form , put text boxs for StartDate and EndDate
and a button to execute
in the button CLICK event paste this code for it to run

Public Sub AddDateRecs()
Dim vDate
Dim sSql As String

DoCmd.SetWarnings False
vDate = txtStartDate
GoSub PostDate   'post 1st day

If Format(vDate, "ddd") <> "Wed" Then
      'get 1st wednesday
    While Format(vDate, "ddd") <> "Wed"
      vDate = DateAdd("d", 1, vDate)
    Wend
    GoSub PostDate
End If

   'post EVERY wednesday
While vDate < txtEndDate
   vDate = DateAdd("d", 7, vDate)
   GoSub PostDate
Wend
DoCmd.SetWarnings True
Exit Sub

PostDate:
sSql = "insert into tWorkouts ClassDate ([ClassDate]) VALUES (" & vDate & ")"
DoCmd.RunSQL sSql
Return
End Sub

[Tommo]

Thanks mate.

Will try in a bit and post back.