Creating a schedule 1

[JosephRoy]

Hi,

I am trying to figure out how to create an athletic schedule from some inputted values. I have a form in which I am having the user enter a start date and number of weeks in the league (event occurs once per week). I would then like to take the start date and figure out the dates of all the other events. For example, the start date entered is 3/17/04. This would be week #1. If the event happens every Wednesday, week #2 would be 3/24/04. If the user enters 10 weeks, I would like to be able to calculate the dates of all 10 Wednesdays and create a table containing the 10 dates and the corresponding week numbers.

Thanks.

 

[thecreator]

Have you tried using the DATEADD function -

Sub CalculateDays()

Dim x As Long, StartDate As Date, NoWeeks As Long

StartDate = Date
NoWeeks = 5

Debug.Print StartDate

For x = 1 To NoWeeks
StartDate = DateAdd("D", 7, StartDate)
Debug.Print StartDate
Next x

End Sub

Hope this helps

 

[JosephRoy]

How, then, would I go about getting the week numbers and dates into a table?

 

[thecreator]

Hi JosephRoy,

Create a new table called tblEvent_Schedule and add the fields -
Event_ID as number
Week_Number as number
Event_Date as date

Create a new form and add the objects -
txtEventID as a text box
txtNoWeeks as a text box
txtStartDate as a text box
btnSchedule as a command button

Add the following code to the <on click> event for the command button -

Private Sub btnSchedule_Click()
Call CalculateDays(txtEventID, txtNoWeeks, txtStartdate)
End Sub

Create a new module and paste the following code to that -

Sub CalculateDays(EventID As Long, NoWeeks As Long, StartDate As Date)

Dim dbs As Database, rst As Recordset
Dim x As Long

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblEvent_Schedule", dbOpenTable)
With rst
For x = 1 To NoWeeks
' write record to table
.AddNew
!Event_ID = EventID
!Week_Number = x
!Event_date = StartDate
.Update

' increment start date
StartDate = DateAdd("D", 7, StartDate)

Next x

End With

' close objects
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

End Sub

Now run your form, enter an event number, the number of weeks that you want and a valid start date and press the command button ... your table will contain a calculated schedule.

I am assuming that you would have another table with an event id, name of the event and what ever other details you record for an event.

Guess you can take it from there ......

Enjoy

 

[dhookom]

Check the "recurring.mdb" sample database at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

It has a form the user can make selections like start and end date as well as every wednesday or the 1st and 3rd thursday or many other combinations.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.