database design

  • Thread starter Thread starter Ian
  • Start date Start date
I

Ian

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
 
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.
Click to expand...

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.
 
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
 
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
 
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.
 
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.
Click to expand...

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?
 
Back
Top