calendar

[zuglchaim]

i have an "event" table.
in this table there 2 fields
field "date"- calendar for year 2003
field "event"- different events which will take plase on this date

i whould like to make in report a regulore calendar with weekdays "sunday", "monday" ,its
and in the "date" box there will be showen an "event" just like we do with a pen on the calendar)
don't forget that sometimes month starets in the middles of the week so a page should display first part of the week with empty boxes
i thought that creating a crosstab query, where colum header will be weekday([date])=vbsunday, its will help, but relised it does not display actual date but rathere counting dates
please help, is there posebility do do sothing lke this without writing 200 line of code?

 

[PWise]

I don’t know if you have a table that save all this info but if all you want is to create a calendar with the month starting at the right day of week create a form in the form header put control for which month and year it is in the detail make five rows of seven text boxes named txtevent1, txtevent2, txtevent3,.....,txtevent35
In the left hand of the each txtevent put a small text box txtday1,txtday2, txtday3,....txtday35
When change the month
Enter this code
Dim mydb as database
Set mydb=currentdb
Dim rst as recoredset
Set rst=mydb.openrecoredset("select top " & daysinmonth(DateSerial(Year, Month, 1)) & " event from event where date >= #" DateSerial(Year, Month, 1) & "#;"
rst.movefirst
dayofweek = Weekday(DateSerial(Year, Month, 1))
for co = 1 to daysinmonth(DateSerial(Year, Month, 1))
me.controls("txtday"& co+dayofweek-1)=co
me.controls("txtevent"& co+dayofweek-1)=rst!event
rst.movenext
next co

Function DaysInMonth(dteInput As Date) As Integer
Dim intDays As Integer

' Add one month, subtract dates to find difference.
intDays = DateSerial(Year(dteInput), _
Month(dteInput) + 1, Day(dteInput))- _
DateSerial(Year(dteInput), _
Month(dteInput), Day(dteInput))
DaysInMonth = intDays
End Function

I think this will do it I did not test this let me know how this went

 

[PWise]

i would like to ammend this a bit this will help that you wont have create a table with all the dates only dates that have events
'1) change recordset to incould the date field
Set rst=mydb.openrecoredset("select top " & daysinmonth(DateSerial(Year, Month, 1)) & " date,event from event where date >= #" DateSerial(Year, Month, 1) & "#;"
rst.movefirst
dayofweek = Weekday(DateSerial(Year, Month, 1))
for co = 1 to daysinmonth(DateSerial(Year, Month, 1))
me.controls("txtday"& co+dayofweek-1)=co
'2) enter event only if date has event
if datepart("d",rst!date)=co then
me.controls("txtevent"& co+dayofweek-1)=rst!event
rst.movenext
end if
next co