Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Header Null for Monthly Calendar (XP)

    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","MonthYea r Is Not Null")
    Waggers
    If at first you do succeed, you've probably missed something.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Header Null for Monthly Calendar (XP)

    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

    <table border=1><td>Nos</td><td>CalendarDay</td><td>DayOfMonth</td><td>WeekdayName</td><td>MonthYear</td><tr><td align=right>1</td><td align=right>

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Header Null for Monthly Calendar (XP)

    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?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Header Null for Monthly Calendar (XP)

    See <!post=Making Tables in the Lounge,162644>Making Tables in the Lounge<!/post> and <!post=Copying a Word or Excel table to a post,164109>Copying a Word or Excel table to a post<!/post>.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Header Null for Monthly Calendar (XP)

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

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Header Null for Monthly Calendar (XP)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •