> Assuming that you're using SQL Server, you could add a > column in the events table to flag how often the event occurs, > then in your sql query retrieve all those for the current month or > day, plus all recurring events, i.e. <snip> > Your cfml code would then cycle through all the events in the query to > determine if a given event should be displayed in the cell for a given day.
I should have mentioned that, if possible, the solution should work in Access. I'm open to slick approaches using SQL specific code, but ideally I want to offer this feature to my clients that are not using SQL. As for your comments above, I actually wrote a calendar program about 4 years ago and I used this type of approach. It works, but it doesn't seem very elegant. The query could potentially return a very large number of irrelevant records, and there are actually two loops to consider: the loop through each "cell" of the displayed calendar (i.e. a given month) plus the loop through the entire recordset. It seems very important to weed out as many irrelvant records as possible at the database. I have been playing with an idea myself, but I'm not sure if it will pan out. I was thinking that I could set a base reference date and then I would store, for each recurring event, the offset of the start date and the recurring increment. For example, if I set the reference date to 1/1/2002 (an arbitrary choice, really) and I wanted to record an event to start today (August 14th) and recur every Wednesday I would use the following pieces of information: Reference date: 1/1/2002 Event start date offset: 225 (225 days have passed between today and Jan 1, 2002) Event increment: 7 (recurs every seven days) Now, to determine if this event occurs on a given day I can use the formula (OffsetOfNewDate-OffsetOfStartDate) MOD Increment If this returns 0 then the event will occur, otherwise it does not occur. For example, to determine if this event occurs next Wed (232 days since Jan 1), which it should, we use: (232-225) MOD 7 -> 7 MOD 7 = 0, so the event will occur. It seems to me that there should be a way to use this information to get a query of all events that will have an occurance between two specified dates. Like, if I know the offsets relative to the reference date of the start and end dates then mathematically I can identify all occuring events by OffsetOfRangeStart <= OffsetOfEventStart + (Increment * x) <= OffsetOfRangeEnd where x > 0. (At least I think that makes sense...) I just can't wrap my mind around getting a SQL query to do what I want it to do. Am I making sense at all here? :) > Just pray they don't ask you for the ability to schedule events that occur > "every third thursday of the month". :) This is on the feature list as well. Ideally my app will work similar to the Yahoo calendar, minus the reminders and invitations and such. I'm not sure how I'd get the idea I mentioned above to do this though. > ahh well... dates have always frustrated me in SQL Server... I hear that. Thanks, Seth Petry-Johnson Argo Enterprise and Associates ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
