By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,978 Members | 1,373 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,978 IT Pros & Developers. It's quick & easy.

Monthly Calendar view as report

P: n/a
Bob
I've searched this group and have not found any posts since 2001, so
I'm hoping that now there may be a better way.

I have a need to generate a report that looks like a outlook monthly
calendar view. What I'm struggleing with is how do I get the first
day of the month to start on the correct day. Also how to fill up the
days in the week prior to the first day with the remaining days of the
previous month.

I've seen some older posts that suggests 42 subreports on the report.
That part is OK, but I don't know how to get the first day of the
month, then all of the other days into the correct subreport for the
day.

Can anyone help with this one?

Thanks in advance,

Bob

Feb 26 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Check out Allen Browne's. al***@allenbrowne.com calendar form.

I used that as a basis for a report, create 41 day fields like the form, add
to that 41 text fields for your information. The following function is
called in the ONFORMAT event of the detail section. The source data
[WORKDAY] and [DESC] is from a record set opened in the ONOPEN event of the
report.

Private Function ShowCal() As Boolean
On Error GoTo Err_Handler
Dim dtStartDate As Date 'First of month
Dim iDays As Integer 'Days in month
Dim iOffset As Integer 'Offset to first label for month.
Dim i As Integer 'Loop controller.
Dim iDay As Integer 'Day under consideration.
Dim bshow As Boolean 'Flag: show label
Dim cDay As Date ' Current working day

dtStartDate = Me![dValue] - Day(Me![dValue]) + 1 'First of month

iDays = Day(DateAdd("m", 1, dtStartDate) - 1) 'Days in month.
iOffset = WeekDay(dtStartDate, vbSunday) - 2 'Offset to first label
for month.

For i = 0 To 41
With Me("lblDay" & Format(i, "00"))
iDay = i - iOffset
bshow = ((iDay 0) And (iDay <= iDays))
cDay = DateAdd("d", iDay - 1, dtStartDate)

.Visible = bshow
Me("txtDay" & Format(i, "00")).Visible = bshow

If (bshow) Then
If iDay < 10 Then
Me("txtDay" & Format(i, "00")) = ".." & iDay
Else
Me("txtDay" & Format(i, "00")) = "." & iDay
End If
If rsInfoOK Then
rsInfo.FindFirst "[WorkDay]=#" & cDay & "#"
While Not rsInfo.NoMatch
' Build up the text string of the day from source
data
Me("txtDay" & Format(i, "00")) = Me("txtDay" &
Format(i, "00")) & _

vbCrLf & Left$(rsInfo![Desc], 40), 0))

rsInfo.FindNext "[WorkDay]=#" & cDay & "#"
Wend
rsInfo.MoveFirst
End If
End If

If (bshow) And (.caption <iDay) Then
.caption = iDay
End If

End With

Next

Exit_Handler:
Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation,
conMod & ".ShowCal"
Resume Exit_Handler
End Function


"Bob" <te*******@bellsouth.netwrote in message
news:11**********************@m58g2000cwm.googlegr oups.com...
I've searched this group and have not found any posts since 2001, so
I'm hoping that now there may be a better way.

I have a need to generate a report that looks like a outlook monthly
calendar view. What I'm struggleing with is how do I get the first
day of the month to start on the correct day. Also how to fill up the
days in the week prior to the first day with the remaining days of the
previous month.

I've seen some older posts that suggests 42 subreports on the report.
That part is OK, but I don't know how to get the first day of the
month, then all of the other days into the correct subreport for the
day.

Can anyone help with this one?

Thanks in advance,

Bob

Feb 26 '07 #2

P: n/a
Bob
On Feb 26, 11:59 am, "paii, Ron" <p...@packairinc.comwrote:
Check out Allen Browne's. a...@allenbrowne.com calendar form.

I used that as a basis for a report, create 41 day fields like the form, add
to that 41 text fields for your information. The following function is
called in the ONFORMAT event of the detail section. The source data
[WORKDAY] and [DESC] is from a record set opened in the ONOPEN event of the
report.

Private Function ShowCal() As Boolean
On Error GoTo Err_Handler
Dim dtStartDate As Date 'First of month
Dim iDays As Integer 'Days in month
Dim iOffset As Integer 'Offset to first label for month.
Dim i As Integer 'Loop controller.
Dim iDay As Integer 'Day under consideration.
Dim bshow As Boolean 'Flag: show label
Dim cDay As Date ' Current working day

dtStartDate = Me![dValue] - Day(Me![dValue]) + 1 'First of month

iDays = Day(DateAdd("m", 1, dtStartDate) - 1) 'Days in month.
iOffset = WeekDay(dtStartDate, vbSunday) - 2 'Offset to first label
for month.

For i = 0 To 41
With Me("lblDay" & Format(i, "00"))
iDay = i - iOffset
bshow = ((iDay 0) And (iDay <= iDays))
cDay = DateAdd("d", iDay - 1, dtStartDate)

.Visible = bshow
Me("txtDay" & Format(i, "00")).Visible = bshow

If (bshow) Then
If iDay < 10 Then
Me("txtDay" & Format(i, "00")) = ".." & iDay
Else
Me("txtDay" & Format(i, "00")) = "." & iDay
End If
If rsInfoOK Then
rsInfo.FindFirst "[WorkDay]=#" & cDay & "#"
While Not rsInfo.NoMatch
' Build up the text string of the day from source
data
Me("txtDay" & Format(i, "00")) = Me("txtDay" &
Format(i, "00")) & _

vbCrLf & Left$(rsInfo![Desc], 40), 0))

rsInfo.FindNext "[WorkDay]=#" & cDay & "#"
Wend
rsInfo.MoveFirst
End If
End If

If (bshow) And (.caption <iDay) Then
.caption = iDay
End If

End With

Next

Exit_Handler:
Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation,
conMod & ".ShowCal"
Resume Exit_Handler
End Function

"Bob" <techno...@bellsouth.netwrote in message

news:11**********************@m58g2000cwm.googlegr oups.com...
I've searched this group and have not found any posts since 2001, so
I'm hoping that now there may be a better way.
I have a need to generate a report that looks like a outlook monthly
calendar view. What I'm struggleing with is how do I get the first
day of the month to start on the correct day. Also how to fill up the
days in the week prior to the first day with the remaining days of the
previous month.
I've seen some older posts that suggests 42 subreports on the report.
That part is OK, but I don't know how to get the first day of the
month, then all of the other days into the correct subreport for the
day.
Can anyone help with this one?
Thanks in advance,
Bob- Hide quoted text -

- Show quoted text -
Thanks for your rapid reply. I'll try it shortly and post my results!

Bob

Feb 26 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.