database design

I am trying to design a database which will store events which occur on the
same days every month for 6 months of the year and this obviously will not
be the same date.

I have some where in the region of 70 diferent organisations for which an
events diary is compiled every year, some have more than one event in one
month although the are on the same day but a different week.

It is a long leborious task changing dates every year and I am sure there
must be some way to do this using access.

Greatful for any help.

Ian

On Mon, 16 Aug 2004 16:20:18 +0000 (UTC), "Ian"
wrote:

I am trying to design a database which will store events which occur on the
same days every month for 6 months of the year and this obviously will not
be the same date.

I have some where in the region of 70 diferent organisations for which an
events diary is compiled every year, some have more than one event in one
month although the are on the same day but a different week.

It is a long leborious task changing dates every year and I am sure there
must be some way to do this using access.

I'm not quite certain what you mean, but take a look at the DateSerial
and DateAdd functions. Open the VBA editor (by typing Ctrl-G for
example) and use the online help for these functions. Just as an
example, if you want to automatically store the 15th of July in the
current year, the function

DateSerial(Year(Date()), 7, 15)

will calculate it for you.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Hi John,

This topic interests me also. I glanced at the help files and I don't
understand how to calculate the third Tuesday of every month (I think that's
similar to what Ian was asking for too).

I've used the immediate window and tried out some variations and here's what
I got:

DateAdd("m", 1, "17-Aug-2004") = 9/14/2004 which is a Friday.
DateAdd("ww", 4, "17-Aug-2004") = 9/14/2004 which is the second Tuesday
DateAdd("d", 28, "17-Aug-2004") = 9/14/2004 which is the second Tuesday

The third Tuesday of every month is a different interval each month -
sometimes it's five weeks, sometimes it's four weeks, so there must be
something else needed to get the result I want. Could you help me out with
that?

tia

rpw

"John Vinson" wrote:

On Mon, 16 Aug 2004 16:20:18 +0000 (UTC), "Ian"
wrote:

I am trying to design a database which will store events which occur on the
same days every month for 6 months of the year and this obviously will not
be the same date.

I have some where in the region of 70 diferent organisations for which an
events diary is compiled every year, some have more than one event in one
month although the are on the same day but a different week.

It is a long leborious task changing dates every year and I am sure there
must be some way to do this using access.

I'm not quite certain what you mean, but take a look at the DateSerial
and DateAdd functions. Open the VBA editor (by typing Ctrl-G for
example) and use the online help for these functions. Just as an
example, if you want to automatically store the 15th of July in the
current year, the function

DateSerial(Year(Date()), 7, 15)

will calculate it for you.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Trythis function...

Function dhNthWeekday(dtmDate As Date, intN As Integer, _
intDOW As Integer) As Date
' Find the date of the specified day within the month.
For
' example, retrieve the 3rd Tuesday's date.
Dim dtmTemp As Date
If (intDOW vbSunday Or intDOW vbSaturday) _
Or (intN 1) Then
' Invalid parameter values. Just
' return the passed-in date.
dhNthWeekday = dtmDate
Exit Function
End If
' Get the first of the month.
dtmTemp = DateSerial(Year(dtmDate), Month(dtmDate), 1)
' Get to the first intDOW in the month.
Do While Weekday(dtmTemp) intDOW
dtmTemp = dtmTemp + 1
Loop
' Now you've found the first intDOW in the month.
' Just add 7 for each intN after that.
dhNthWeekday = dtmTemp + ((intN - 1) * 7)
End Function


-----Original Message-----
Hi John,

This topic interests me also. I glanced at the help
files and I don't
understand how to calculate the third Tuesday of every
month (I think that's
similar to what Ian was asking for too).

I've used the immediate window and tried out some
variations and here's what
I got:

DateAdd("m", 1, "17-Aug-2004") = 9/14/2004 which is a
Friday.
DateAdd("ww", 4, "17-Aug-2004") = 9/14/2004 which is the
second Tuesday
DateAdd("d", 28, "17-Aug-2004") = 9/14/2004 which is the
second Tuesday

The third Tuesday of every month is a different interval
each month -
sometimes it's five weeks, sometimes it's four weeks, so
there must be
something else needed to get the result I want. Could
you help me out with
that?

tia

rpw

"John Vinson" wrote:

On Mon, 16 Aug 2004 16:20:18 +0000 (UTC), "Ian"
wrote:

I am trying to design a database which will store
events which occur on the
same days every month for 6 months of the year and
this obviously will not
be the same date.

I have some where in the region of 70 diferent
organisations for which an
events diary is compiled every year, some have more
than one event in one
month although the are on the same day but a different
week.

It is a long leborious task changing dates every year
and I am sure there
must be some way to do this using access.

I'm not quite certain what you mean, but take a look at
the DateSerial
and DateAdd functions. Open the VBA editor (by typing
Ctrl-G for
example) and use the online help for these functions.
Just as an
example, if you want to automatically store the 15th of
July in the
current year, the function

DateSerial(Year(Date()), 7, 15)

will calculate it for you.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

.

Hi Cheval,

Thank you for the function. I'm going to plug it into a module and
experiment with it until I get an understanding of how it's working. And
thank you for including explanitory comments in the function, that makes
understanding the function easier.

"Cheval" wrote:

Trythis function...

Function dhNthWeekday(dtmDate As Date, intN As Integer, _
intDOW As Integer) As Date
' Find the date of the specified day within the month.
For
' example, retrieve the 3rd Tuesday's date.
Dim dtmTemp As Date
If (intDOW vbSunday Or intDOW vbSaturday) _
Or (intN 1) Then
' Invalid parameter values. Just
' return the passed-in date.
dhNthWeekday = dtmDate
Exit Function
End If
' Get the first of the month.
dtmTemp = DateSerial(Year(dtmDate), Month(dtmDate), 1)
' Get to the first intDOW in the month.
Do While Weekday(dtmTemp) intDOW
dtmTemp = dtmTemp + 1
Loop
' Now you've found the first intDOW in the month.
' Just add 7 for each intN after that.
dhNthWeekday = dtmTemp + ((intN - 1) * 7)
End Function


-----Original Message-----
Hi John,

This topic interests me also. I glanced at the help
files and I don't
understand how to calculate the third Tuesday of every
month (I think that's
similar to what Ian was asking for too).

I've used the immediate window and tried out some
variations and here's what
I got:

DateAdd("m", 1, "17-Aug-2004") = 9/14/2004 which is a
Friday.
DateAdd("ww", 4, "17-Aug-2004") = 9/14/2004 which is the
second Tuesday
DateAdd("d", 28, "17-Aug-2004") = 9/14/2004 which is the
second Tuesday

The third Tuesday of every month is a different interval
each month -
sometimes it's five weeks, sometimes it's four weeks, so
there must be
something else needed to get the result I want. Could
you help me out with
that?

tia

rpw

"John Vinson" wrote:

On Mon, 16 Aug 2004 16:20:18 +0000 (UTC), "Ian"
wrote:

I am trying to design a database which will store
events which occur on the
same days every month for 6 months of the year and
this obviously will not
be the same date.

I have some where in the region of 70 diferent
organisations for which an
events diary is compiled every year, some have more
than one event in one
month although the are on the same day but a different
week.

It is a long leborious task changing dates every year
and I am sure there
must be some way to do this using access.

I'm not quite certain what you mean, but take a look at
the DateSerial
and DateAdd functions. Open the VBA editor (by typing
Ctrl-G for
example) and use the online help for these functions.
Just as an
example, if you want to automatically store the 15th of
July in the
current year, the function

DateSerial(Year(Date()), 7, 15)

will calculate it for you.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

.

hi all

I am stil in trouble with this most obviously due to my lack of knowledge.

Not to sure what to do with the function or how to get it to work for me.

need more help.
"Ian" wrote in message
...
I am trying to design a database which will store events which occur on
the
same days every month for 6 months of the year and this obviously will not
be the same date.

I have some where in the region of 70 diferent organisations for which an
events diary is compiled every year, some have more than one event in one
month although the are on the same day but a different week.

It is a long leborious task changing dates every year and I am sure there
must be some way to do this using access.

Greatful for any help.

Ian

On Tue, 17 Aug 2004 21:34:44 +0000 (UTC), "Ian"
wrote:

hi all

I am stil in trouble with this most obviously due to my lack of knowledge.

Not to sure what to do with the function or how to get it to work for me.

Ian, part of the problem is that you have not clearly stated just what
you are trying to accomplish. You say:

I have some where in the region of 70 diferent organisations for
which an events diary is compiled every year, some have more than one
event in one month although the are on the same day but a different
week.

You certainly know what you mean by "on the same day but a different
week", but I for one do not.

Could you post an example of the types of dates you want?

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public