What kind of date format for repeating events?

[Bruce Meneghin]

I have searched in vain for hints on how to store date information about
repeating events: birthdays, anniversaries, meetings held on the second tues.
of each month etc.
My intended application is to have a table of these one-day events and be
able to determine if an event occurs on a specific date. Ideally, I would be
able to query a table of events and return a list of events on a given date
or within an interval of dates. I would settle for a function that would
return true/false when asked
is eventDate == thisDate

Bruce

 

[Klatuu]

You have a couple of things mixed there. Birthdays and anniversaries are
both dates, but neither has a time component, so you would use a date data
type but not store a time in it. Appointments, on the other hand, do have a
time and should be stored in a Date field.

Now as to the meeting on the second Tuesday of each month. That is not
actually a date, so can't really be stored as a date. Here it gets tricky.
I haven't done any extensive study on best practices for this and others may
be more knowledgable on the best way to store the data, but what I can say is
you can determine the date of the second Tuesday using some date handling if
you know the month and year you want.

As to determining if an event is today:

If Not IsNull(DLookup("[EventDate]", "tblEvents", "[EventDate] = #" &
Date & "#")) Then
MsgBox "Today is the Big Day!"
End If

 

[Bruce Meneghin]

I need a little more. Let's limit this to dates, not time. How would I
store a birthday of April 15 (any and every year) or a billing date of 21st
day (of each and every month)? Can I do this without generating a table with
records for each specific mmddyyyy that the repeating event occurs?

You have a couple of things mixed there. Birthdays and anniversaries are
both dates, but neither has a time component, so you would use a date data
type but not store a time in it. Appointments, on the other hand, do have a
time and should be stored in a Date field.

Now as to the meeting on the second Tuesday of each month. That is not
actually a date, so can't really be stored as a date. Here it gets tricky.
I haven't done any extensive study on best practices for this and others may
be more knowledgable on the best way to store the data, but what I can say is
you can determine the date of the second Tuesday using some date handling if
you know the month and year you want.

As to determining if an event is today:

If Not IsNull(DLookup("[EventDate]", "tblEvents", "[EventDate] = #" &
Date & "#")) Then
MsgBox "Today is the Big Day!"
End If
--
Dave Hargis, Microsoft Access MVP

I have searched in vain for hints on how to store date information about
repeating events: birthdays, anniversaries, meetings held on the second tues.
of each month etc.
My intended application is to have a table of these one-day events and be
able to determine if an event occurs on a specific date. Ideally, I would be
able to query a table of events and return a list of events on a given date
or within an interval of dates. I would settle for a function that would
return true/false when asked
is eventDate == thisDate

Bruce

 

[John W. Vinson]

I need a little more. Let's limit this to dates, not time. How would I
store a birthday of April 15 (any and every year) or a billing date of 21st
day (of each and every month)? Can I do this without generating a table with
records for each specific mmddyyyy that the repeating event occurs?

What's wrong with doing that? You can do it for the next ten years with a
couple of thousand records - a tiny table. And you can even create the records
in VBA code, or evein using Fill... Series in Excel and copying and pasting.

A date/time field refers to a precise instant in time. It's stored as a double
float count of days (and fractions of a day, times) since midnight, December
30, 1899; as such "April 15" or "the 21st day of the month" is not a date
value by itself.

 

[Bruce Meneghin]

OK, for the final calendar generation, I'll query a generated table of
precise instances in time. How about a table structure for storing the event
information so that events can be added, deleted, edited? This table would be
used as the source for the routine that generates the "actual dates".

 

[John W. Vinson]

OK, for the final calendar generation, I'll query a generated table of
precise instances in time. How about a table structure for storing the event
information so that events can be added, deleted, edited? This table would be
used as the source for the routine that generates the "actual dates".

I'm not sure I understand the question. The events table would have a
date/time field for the scheduled event.

I guess you could imagine two tables, RepeatingEvents and Events. The
RepeatingEvents table might have fields like:

EventID
EventName
StartDate
RecurranceInterval
RecurranceFrequency
Recurrances

with values like

EventName: Monthly sales meeting
StartDate: 4/21/2008
RecurranceInterval "M" <monthly>
RecurranceFrequency 1 <every month>
Recurrances 1200 <"forever">

EventName Quarterly Review
StartDate 6/1/2008
RecurranceInterval "M"
RecurranceFrequency 3 <every three months>
Recurrances 400 <ten years again>

You'll need some VBA to ensure that monthly events' dates get tweaked to the
Friday before or the Monday after a weekend date, I'd guess...