days and dates

Topic

New Reply

Here’s what I’m looking for.

Given a start date and an end date, and the days of the week a class would meet, and have Access calculate and populate a table with all the correct dates.

For example, a class is to start on 4/30/01 and go until 5/11/01, and it meets on Mon, Wed, and Fri. Thus, at the click of a command button, Access would populate a table (Tbl_Meeting_Dates) with 6 rows, where the
MeetingDate field of Tbl_Meeting_Dates would contain these dates:

4/30/01
5/1/01
5/5/01
5/7/01
5/9/01
5/11/01

(There would also be a foreign key field to relate back to the tbl_Class_Name.)

Classes could also meet just one day a week, on the weekends and/or weekdays, or on Mon. and Thurs. for 6 weeks, etc.

And ideas?

Thanks.

—
Bill

Reply | Quote

Replies

Bill, just a suggestion, it may be easier to do this in Excel. Of course, in depends on the number of classes that you have to schedule.

HTH.

Reply | Quote

Perhaps, but I’m writing an entire application in Access, and this is just a small part of the program.

Maybe if I could call Excel from Access, have it do it’s work, and then put the data back in Access…

Reply | Quote

Hi Bill,

Well, sorry to say but you succeeded in confusing me.
So, let’s deal with that first. You say the class would meet from Apr. 30/2001 to May 11/2001 and only on Monday, Wednesday and Friday.

Looking at my calendar for the year 2001, it looks like this:
Monday – Apr 30
Wednesday – May 2
Friday – May 4
Monday – May 7
Wednesday – May 9
Friday – May 11.
So, I don’t understand where May 1 and May 5 come from.

However, dealing with a solution:
You need two date boxes, Start and Ending Date and a panel with seven checkboxes, one for each day, named Monday, Tuesday, Wednes…etc.

When you click the command button, the code loops through each date in the date range and, using a function to determine the Day name for that date, compare the Day name against the checkboxes. If the Day name matches with a box that is checked, you add a record to the table and populate the fields with whatever they should contain.

Just an idea.

Reply | Quote