Dates between two dates

[D. Mullins]

I have a DB for tracking education classes. Some classes meet once/week for a set period time between two dates--StartDate and EndDate. Some courses meet twice/week. Some courses only meet once--StartDate and EndDate are equal. I want a report which will list all of the days which the classes will meet (format: Monday, 10/27/03). I can write expressions in a query to determine how many days/week the class meets--FirstDayatePart("w",DateStart) and LastDay: DatePart("w",DateEnd). If FirstDay=LastDay, then 1day/week; otherwise 2 days/week. Also can calculate how many weeks the class will meet: DateDiff("ww",StartDate, EndDate). Now what I need to know is how to use this information to list the dates. I'm thinking some actual code will need to be written

 

[Allen Browne]

Instead of storing the StartDate and EndDate for the classes, consider
creating a record for each of the dates when the class is due to meet. You
will need those records for matching up the attendees at each class, and
when there is a public holiday you can delete the particular record (or
adjust the date if the class does meet on the Tuesday instead of the Monday
that week).

To programatically add the records to the table, it's probably best to
OpenRecordset and AddNew (with Update).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a DB for tracking education classes. Some classes meet once/week

for a set period time between two dates--StartDate and EndDate. Some
courses meet twice/week. Some courses only meet once--StartDate and EndDate
are equal. I want a report which will list all of the days which the
classes will meet (format: Monday, 10/27/03). I can write expressions in a
query to determine how many days/week the class
meets--FirstDayatePart("w",DateStart) and LastDay: DatePart("w",DateEnd).
If FirstDay=LastDay, then 1day/week; otherwise 2 days/week. Also can
calculate how many weeks the class will meet: DateDiff("ww",StartDate,
EndDate). Now what I need to know is how to use this information to list
the dates. I'm thinking some actual code will need to be written.

 

[D. Mullins]

I opt for making a new table--but my problem is how.

 

[Allen Browne]

If you are not familiar with adding records to a table programmatically,
here is a simple example.
You would need to create your loop so it adds just the dates you need:

Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2002# To #12/31/2002#
.AddNew
!WotDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

 

[D. Mullins]

Used your function/tested it--understand ok. Thank You! What I want to do is
1. Use an input form to gather a start date and an end date for my table--I want all the class meeting dates which fall in November (#11/1/2003# To #11/30/2003#)
2. Use a query which has the Start Date and End Date for all the classes. (Each record is a separate class with StartDate and EndDate as fields.--Cycle these records through the "For Statement" of the Function MakeDates()
A. If StartDate and EndDate are the same, the new record consists of only StartDate
B. If PartDate("w",StartDate)=PartDate("w",EndDate), then the class meets on the same day of the week; and
meets for DateDiff("ww",[StartDate],[EndDate]) times; therefore there will be "ww" records to add.
C. If PartDate("w",StartDate)<>PartDate("w",EndDate), then the class meets on two days during the week-
PartDate("w", StartDate) and PartDate("w",EndDate) and the same logic as "B" for how many weeks (times 2)
D. There are NO classes that meet more than twice per week. (Thank goodness!--These are only community classes!
As the For statement cycles through the records, determines the class meeting dates for each record, it then compares the resulting date against for Start Date and End Date from the input form (#1) if the date is >= Start Date and <= End Date THEN the ClassDate is added to the new tbl (along with another field from that same record)

 

[D. Mullins]

More thinking--but need a LOT of refinement! Need help with how to cycle through the query--Syntax, etc. for If's and Fors
I'm pretty green at this! Only Computer I under my belt
StartDate and EndDate are fields from my query. MonthStartDt and MonthEndDt are text box data from user input form

If StartDate = EndDate And StartDate>=MonthStartDt And StartDate <=MonthEndD
dt=Query.CourseClass.StartDat
.AddNe
!ClassDate=d
.Updat
Else For dt = StartDate To EndDat
If DatePart("w",StartDate)=DatePart("w",dt) o
If DatePart("w",EndDate)=DatePart('w",dt) An
If dt >=MonthStartDt And <=MonthEndD
.AddNe
!ClassDate=d
.Updat
Next

 

[Allen Browne]

Okay.

We can't write your code and sort out all the nuances/details for you, but
what are are proposing sounds like a useful way to approach this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

More thinking--but need a LOT of refinement! Need help with how to cycle

through the query--Syntax, etc. for If's and Fors.

I'm pretty green at this! Only Computer I under my belt!
StartDate and EndDate are fields from my query. MonthStartDt and

MonthEndDt are text box data from user input form.

 

[D. Mullins]

Would you mind commenting on my poor attempt at Code....I AM VERY APPRECIATIVE!!!
‘Function uses two tables—tblClasses (source table) and tblClassDates (table where info is written
‘cycles through the source table--manipulates Start and End Dates to determine if a Class Date is held during 'the month

Function MakeDates(

Dim MyDb As Databas
Dim MyTable1 As DAO.Recordse
Dim MyTable2 As DAO.Recordse
Dim dt As Dat
Dim id As Long Intege

Set MyDb = DBEngine.Workspace(0).Databases(0

‘Open Table
Set MyTable1=MyDb.OpenRecordset(“tblClassesâ€, DB_OPEN_TABLE
Set MyTable2=MyDb.OpenRecordset(“tblClassDates,DB_OPEN_TABLE

Do Until MyTable1.EO
‘Meets only once & within date rang
If MyTable1![StartDate]=MyTable1![EndDate] And (MyTable1![StartDate] >=#11/1/2003# An
MyTable1![StartDate] <= #11/30/2003#
dt = MyTable1![StartDate
id = MyTable1![ScheduleID
With MyTable
AddNe
!ScheduleID=i
!ClassDate=d
.Updat
End Wit
‘Meets weekly—either once/week or ‘twice/week & within date rang
Else
For dt = MyTable1![StartDate] To MyTable1![EndDate
If DatePart(“wâ€, MyTable1![StartDate]=DatePart(“wâ€, dt) o
DatePart(“wâ€,MyTable1![EndDate]=DatePart(“wâ€, dt) And (MyTable1![StartDate] >=#11/1/2003# An
MyTable1![StartDate] <= #11/30/2003#
id = MyTable1![ScheduleID
With My Table
AddNe
!ScheduleID=i
!ClassDate=d
.Updat
End Wit
Nex
MyTable1.Clos
MyTable2.Clos
End Functio

tblClasses (Source Table
ScheduleID StartDate EndDat
1 9/27/2003 9/27/200
2 11/18/2003 12/23/200
3 10/27/2003 12/8/200
4 10/27/2003 12/8/200
5 11/6/2003 11/6/200
6 11/24/2003 11/24/200
7 12/1/2003 12/1/200
8 11/15/2003 12/20/200
10 10/16/2003 11/20/200
11 10/30/2003 12/11/200
12 11/10/2003 12/15/200
13 11/15/2003 11/16/200
14 11/5/2003 12/17/200

Results
tblClassDate
ScheduleID ClassDat
2 11/18/200
2 11/25/200
3 11/3/200
3 11/10/200
3 11/17/200
3 11/24/200
4 11/3/200
4 11/10/200
4 11/17/200
4 11/24/200
5 11/6/200
6 11/24/200
8 11/15/200
8 11/22/200
8 11/29/200
10 11/6/200
10 11/13/200
10 11/20/200
12 11/10/200
12 11/17/200
12 11/24/200
13 11/15/200
13 11/16/200
14 11/5/200
14 11/12/200
14 11/19/200
14 11/26/200

 

[Allen Browne]

For 2ce weekly, you will probably need to specify which days of the week,
and run the loop twice.

Testing the code in Access will help identify your errors (e.g. missing
brackets).

Avoid DB_OPEN_TABLE if possible: it's not compatible with a split database.
Also, consider using the newer style constants.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Would you mind commenting on my poor attempt at Code....I AM VERY APPRECIATIVE!!!!
'Function uses two tables-tblClasses (source table) and tblClassDates (table where info is written)
'cycles through the source table--manipulates Start and End Dates to

determine if a Class Date is held during 'the month.

Function MakeDates()

Dim MyDb As Database
Dim MyTable1 As DAO.Recordset
Dim MyTable2 As DAO.Recordset
Dim dt As Date
Dim id As Long Integer

Set MyDb = DBEngine.Workspace(0).Databases(0)

'Open Tables
Set MyTable1=MyDb.OpenRecordset("tblClasses", DB_OPEN_TABLE)
Set MyTable2=MyDb.OpenRecordset("tblClassDates,DB_OPEN_TABLE)

Do Until MyTable1.EOF
'Meets only once & within date range
If MyTable1![StartDate]=MyTable1![EndDate] And (MyTable1![StartDate]
=#11/1/2003# And
MyTable1![StartDate] <= #11/30/2003#)
dt = MyTable1![StartDate]
id = MyTable1![ScheduleID]
With MyTable2
AddNew
!ScheduleID=id
!ClassDate=dt
.Update
End With

'Meets weekly-either once/week or 'twice/week & within date range

Else
For dt = MyTable1![StartDate] To MyTable1![EndDate]
If DatePart("w", MyTable1![StartDate]=DatePart("w", dt) or
DatePart("w"

,MyTable1![EndDate]=DatePart("w", dt) And (MyTable1![StartDate]

=#11/1/2003# And
MyTable1![StartDate] <= #11/30/2003#)
id = MyTable1![ScheduleID]
With My Table2
AddNew
!ScheduleID=id
!ClassDate=dt
.Update
End With
Next
MyTable1.Close
MyTable2.Close
End Function

tblClasses (Source Table)
ScheduleID StartDate EndDate
1 9/27/2003 9/27/2003
2 11/18/2003 12/23/2003
3 10/27/2003 12/8/2003
4 10/27/2003 12/8/2003
5 11/6/2003 11/6/2003
6 11/24/2003 11/24/2003
7 12/1/2003 12/1/2003
8 11/15/2003 12/20/2003
10 10/16/2003 11/20/2003
11 10/30/2003 12/11/2003
12 11/10/2003 12/15/2003
13 11/15/2003 11/16/2003
14 11/5/2003 12/17/2003

Results:
tblClassDates
ScheduleID ClassDate
2 11/18/2003
2 11/25/2003
3 11/3/2003
3 11/10/2003
3 11/17/2003
3 11/24/2003
4 11/3/2003
4 11/10/2003
4 11/17/2003
4 11/24/2003
5 11/6/2003
6 11/24/2003
8 11/15/2003
8 11/22/2003
8 11/29/2003
10 11/6/2003
10 11/13/2003
10 11/20/2003
12 11/10/2003
12 11/17/2003
12 11/24/2003
13 11/15/2003
13 11/16/2003
14 11/5/2003
14 11/12/2003
14 11/19/2003
14 11/26/2003