Report Header Null for Monthly Calendar (XP)

Topic

New Reply

Hi Tom

Sounds like a job for a DLookup! In design view in your report, change the entry in your MonthYear text box to:

=DLookup(“MonthYear”,”qryCreateCalendar”,”MonthYear Is Not Null”)

Reply | Quote

Replies

Edited by HansV to present info in table format.

Goal: Create report giving a monthly calendar with the report header containing the seven days of the week and the month/year.

rptMonthlyCalendar3
The report works for the date 1/1/06 because January 1, 2006 begins on Sunday.
However since other months do not start on Sunday the MonthYear is missing from them.
I could use a text box with the source as [Enter Month Name and Year], but I do not want to include this extra step.

I need code to progress through each record until the field MonthYear is not null and the result is put into the header text box.

qryCreateCalendar
PARAMETERS [Enter first date of month] DateTime;
SELECT tblNos.Nos, IIf(Month([Enter first date of month]+[Nos]-Weekday([Enter first date of month]))=Month([Enter first date of month]),[Enter first date of month]+[Nos]-Weekday([Enter first date of month]),Null) AS CalendarDay, Day([CalendarDay]) AS DayOfMonth, [CalendarDay] AS MonthYear
FROM tblNos
ORDER BY tblNos.Nos;

Results of Query

Nos	CalendarDay	DayOfMonth	WeekdayName	MonthYear
1

Reply | Quote

Thanks for the reply. I have tried permutations of syntax but can not get the code to work.

By the way, thanks Hans for the formatting. How do you do it, insert a table?

Reply | Quote

See Making Tables in the Lounge and Copying a Word or Excel table to a post.

Reply | Quote

Why not use [Enter first date of month] itself to display the month and year on the report?

Reply | Quote

Eureka! It works.

Entering the following – [Enter first date of month] – as the control source for the text box in the report header works.

Thank you.

Reply | Quote