Strange request (1 Viewer)

[David R]

I work in a government office that interfaces with community groups on a regular basis. I've been asked to provide a calendar 'report' which lists all the meetings and what day they fall on for the rest of the year.

The data is currently stored as "Second Saturday of Every Other Month", "Quarterly on the Last Friday", etc, in other words completely textual and not standardized. If I can decipher how to make Access read this and make sense, I could finish this idea.

I know something like this is possible because Outlook and Lotus Notes both have calendar options that 'repeat'. That is what I am after, but hopefully tying it into our existing list of Neighborhoods. I have freedom to modify the fields to suit what I need to do, and currently it looks something like this:
[MeetingFreq]: "Weekly";"Every Other Week";"Every Month";"Every Other Month";"Every Three Months";"Every Four Months";"Every Six Months";"Annually"
[MeetingOrder]: "First";"Second";"Third";"Fourth";"Last";"Second to Last";"Every"
[MeetingDay]: "Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"
[MeetingTime]: (Date/Time field)

Any ideas or resources to suggest?

Thanks in Advance,
David

 

[Rich]

For how long do you need to repeat for, 1year 2, etc?

 

[David R]

Ideally it would store in such a way that I could ask it for a report on, say, September 2002 to July 2003, and it would calculate who meets when and spit it all out into a report.

For the short term, my assignment is August - December 2002.

 

[The_Doc_Man]

Unfortunately, you are dealing with English syntax, which is not noted to be the easiest of all languages for decoding text.

You have two distinct problems. First, you need to scan text to separate out the words into distinct parts. This is easy enough with a little bit of work using InStr and the Mid function to take apart the string. But what comes next is tougher.

Once you have the syntax right, you STILL need to decipher the semantics of the statement. Let me ramble through some thoughts on this problem because I faced it another way on another machine (that WASN'T running Access.)

Suppose your frequency is "Every week on Thursday at Noon"

This one is easy because "Every week" can be equate to "Weekly" - which specifies the period. Thursday nails down the day of the week. Noon nails down the time.

But now lets talk about "Every other week on Monday at 10 AM"

Well, the Monday and 10 AM are clear enough. "Every other week" can be equated to "Per fortnight." (Is there such a word as fortnightly?) But there is still an ambiguity left in these semantics. Did you mean the odd weeks or the even weeks of the year? Or did you mean the odd weeks or even weeks of the month AND RESET THE "TIMER" at the beginning of each month?

Then, of course, there are the algorithmic issues of deciding which day is the correct day. Suppose the meeting is "The second Monday of every month at 1 PM." Again, the 1PM is clear enough - but how do you decide that a particular Monday is the second Monday of the month. (I'll answer that in a moment...)

OK, how about "Wednesday of the second week of the month at 2 PM" - the time is easy, but which week is the second week? Do you mean "the second full week" or does a partial week count? And if so, HOW MUCH of the week has to be present to make it a week for counting purposes in this specification?

You can see that you have some complexities of the English language standing in your way. Not only do you have to parse your dates but you have to do semantic analysis. And then, common phrases that a manager might use would leave you with ambiguities anyway.

OK, the second Monday of the month is the Monday that has a date (day of the month) between 8 and 14, inclusive.

Now: I solved my problem by not allowing full English input. In my scheduler, you can specify any time of the day for periods of

DAILY - meaning 7 days a week
WEEKLYday1, day2, ....) - meaning once per week on the listed days, and this is how you would do 5 days per week.
MONTHLYday-number-1, day-number-2, etc) - how you do once per month, and I recognize LAST as a valid day number.
YEARLYmonth:day,month:day,...) - how you pick a particular date.

I also have HOURLYminutes-past, minutes-past,...) - as a way for my computer to run certain scan jobs at the correct time. And DAILYtime-1, time-2, ...) works for me. But this wasn't done in Access. The only reason I tell you about it is because of the implications it has for the semantics you need to parse.

 

[David R]

I have the freedom to change the parsing as I like...

The data is just getting started to be stored. We only have about 20% done.

My current options are two-fold:
1) Store the meeting dates in whatever format I need to in order to cause it to repeat them as needed. I am simply stuck on a way to do this, short of brute-forcing it to check every possible date until it matches. I can do this in C, but pushing it to a report in Access with VBA? I'm not that smart yet...

2) Create a public calendar/database in Lotus Notes and use its repeating calendar functions to do exactly this. If I could look at the guts of how Lotus/Outlook do this, I might be able to recreate it in Access. Of course their source is closed, so....

So if there is a way for me to see what you did in your database (the calendar bit, not the parsing of your fields) I would be very grateful for a launching pad. What format is it in?

 

[Amy35]

some ideas

Logically I could figure this out, but couldn't write a lick of code for it. You are right though, it can be done, my PDA does this.

Some thoughts:
Assign values to
MeetFreq; MeetingOrder; MeetingDay and MeetingTime
Then perform a calculation based on the value assiged to those fields.
MeetingFreq =weekly and MeetingOrder =First and MeetingDay =Monday and MeetingTime=9:00

Then MeetingDate would equal some sort of calculation using the current calendar as criteria. That's where I'm stumped...I can conceptualize it but I can't write the code. Could you use the Julian date converter? Somehow with the numbers you could add 14 for every 2 weeks etc. Then convert it back to the a date. I'm sure there's stuff on the Internet for converting back and forth.

Hopefully something here will help. Good Luck

Amy

 

[The_Doc_Man]

Unfortunately, it wasn't a database situation. It was a way to implement the OpenVMS equivalent of the UNIX cron utility. It was a home-grown system utility program. Part of it was scripted, part was compiled code. I'll share with you what experiences I had with the scheduling algorithm. I don't know if you can implement it this way, but it might help you to clarify your thinking on the issue.

The syntax I used was designe to allow me to specify certain timing limits, then parse out the meta-commands for my script fragments, then split out the fragments that were relevant at a given moment. When the parsing was complete, anything that I didn't filter out represented a due (or overdue, see later) event.

The program kept track of the last time it had run to completion. It iterated over the time interval between the last run and the current run in increments of (in my case) 15 minutes. For each time in the interval, the schedule was tested to determine if the associated event should have occurred. The interval was visited using the rule: (Begin..End] - which means include the end time in the scan but not the beginning time. This is also called "open beginning, closed end" (or vice versa, I can never remember which way that is supposed to be.)

The syntax I supported for a scheduled event (remember, this is a program execution scheduler) was

/EVERY - meaning due every time the scheduler runs, which was 15 minutes in my case.

/HOURLYminutes, minutes....) - meaning due every hour at the specified minutes past the hour. Since I used 15 minute intervals, the PRACTICAL times in minutes were 00, 15, 30, and 45. Nothing else meant anything.

/DAILYhrs:minutes, hrs:minutes...) - meaning due every day at the specified times. Same limits on minutes as for HOURLY.

/WEEKLYwkday:hrs:minutes, wkday:hrs:minutes...) - meaning due once per week on the given day of the week at the specified time. Same limits on minutes as for HOURLY. This is how you would make some weekend only or weekday only.

/MONTHLYday:hrs:minutes, day:hrs:minutes...) - meaning once per month on the specified day of the month at the specified time. In this context, LAST is counted as a proper synonym for the last day of the month regardless of the current month. Same limits on minutes as for HOURLY.

/YEARLYmonth:day:hrs:minutes, month:day:hrs:minutes...) - meaning once per year on the given date at the given time. Again, LAST counts as a day of the month. Same limits on minutes as for HOURLY.

For the parenthetical entries, if there was only one time for an event, then I dropped the requirement for parentheses. Only if you had more than one time in the same sequence did you need the parentheses and comma. Defaults were: For month, JAN. For day, 1. For hour, 0. For minute, 0. Therefore, /YEARLY (with no arguments) was midnight January 1. The rule also said that you had to include colons up to the first argument that WASN'T the default value. So for example, /YEARLY::::30 meant January 1st at half-past midnight. (Defaults for month, day, and hour.) But /YEARLY:::1 meant January 1 at 1:00 AM (because you could omit trailing colons when you wanted the default for that field.)

I also had to specify what to do if the scheduler had been idle for a while due to system down time for maintenance or after a program failure. In this case, it was possible that some of my scheduled events had become overdue.

/TIMING: was my switch option for overdue event specification, giving me three choices:

ONCE = schedule the overdue event immediately, but only run one activation no matter how many events were missed.

EXACT = schedule overdue events ONLY at the indicated times. If the event was missed, it was missed. Tough luck.

MULTIPLE = schedule the overdue event once for each time that it should have been, but wasn't, run.

Just as a side effect of the way this code was written, it was possible to specify impossible times. My parser caught that and kicked it out when it was first specified. So you could not write a date such as /YEARLY:FEB:30

However, other times might or might not exist. The scheduler had to be more lenient in some cases.

For example, /MONTHLY:28 is always valid. /MONTHLY:29 is always valid only in leap years. But because at least SOME months have a 29th in them, I allowed it. Ditto for 30 and 31, since at least some months have those days, too. But /MONTHLY:32 was always illegal. In this context, I had a special rule that said /MONTHLY:LAST, which was implemented by seeing if today + 1 day = the first of next month. If so, then today is the last day of this month. (In Access, you can do this by doing a DATEPART of the result of a DATEADD based on today's date.)

I don't know how much of this will be helpful, but this is the way I made it work for a system that didn't have a time-based event scheduler for repetitive event scheduling. If I gave you the code, it would be highly specific for an OpenVMS environment, so you would have a devil of a time adapting it. Also, there is a small issue of the fact that I also work at a government site. You know how tricky it is to exchange code between agencies even when no national security issues exist.

Good luck, my friend. Have fun with your scheduler.

 

[Freddy]

Why not use Outlook or your preferred calendar app instead of building scheduling functionality?

 

[The_Doc_Man]

Outlook wasn't (and still isn't) available to me for the big scheduler that I had to build. But I agree with Freddy that you might be better off with Outlook's scheduler than anything you could do in Access.

 

[David R]

Our office doesn't use Outlook

and as anyone who has used it knows, Lotus Notes' calendar printing ability is horrible. I might look into pushing the data into Lotus Notes to use their scheduler, then bringing it back to Access to create the report from the Notes database...

Unless anyone has any brighter ideas?