Unbound field in report Page Header Script to loop date and print

[savy]

Hi, trying to print a report which covers two pages with the Date for each day of the month to print in Page Header. I have it working in Excel fine by code, however, I cannot configure for Access Report.

Example below, the unbound text box to display 1st day of month print, then display 2nd day through to end of month.

any help appreciated. thanks in advance.

[savy]

attached image of report with unbound field for the date loop

[Micron]

Impossible to definitively say without seeing any data or what the report is based on. IMO you'll need a date value for each record, then group each page by date. If you want the control in the report to show "Monday" then you'll need to parse that from (e.g.) 01/13/2025 using something like format(#01/13/2025#,"dddd") where the date I posted is probably going to be a field in the underlying table or query for the report. Format will coerce date data type to a string, but for a report, I don't see that being an issue.

[savy]

Impossible to definitively say without seeing any data or what the report is based on. IMO you'll need a date value for each record, then group each page by date. If you want the control in the report to show "Monday" then you'll need to parse that from (e.g.) 01/13/2025 using something like format(#01/13/2025#,"dddd") where the date I posted is probably going to be a field in the underlying table or query for the report. Format will coerce date data type to a string, but for a report, I don't see that being an issue.

Ideally looking for code that will set focus to the report. then loop the print with update of the unbound text box in the Page Header to the new date. If I use the unbound text box to feed the date from loop then obviously I don't need to code the formatting etc. Just which to achieve similar but to the report I showed.

example Excel code which works below: wish to apply the closest fit to an Access Report, which I cant kick over.


Sub datesinPageHeader()
Dim DateString As String
Dim CopiesCount As Long
Dim CopieNumber As Integer
Dim BeginDate As Date
Dim countiDate As Date
Dim N As Integer


BeginDate = DateValue("1 December, 2024")
DateString = Format(BeginDate, "dddd, mmm d yyyy")
N = 1





For CopieNumber = 1 To 31
With ActiveSheet

.PageSetup.CenterHeader = "&""Arial,Bold""&18DATE: " & DateString


.PrintOut

End With
N = N + 1
BeginDate = BeginDate + 1
DateString = Format(BeginDate, "dddd,d mmm yyyy")
Next CopieNumber


ActiveSheet.PageSetup.CenterHeader = ""


End Sub

[June7]

You want this report to output for each day of the month and the page header to show that date?

Could you provide db for analysis? Follow instructions at bottom of my post.

[savy]

You want this report to output for each day of the month and the page header to show that date?

Could you provide db for analysis? Follow instructions at bottom of my post.

Hi thanks. It will take me some days as at work and will need to cleanse. Basically just want to print the same report document for total days of month at once.

I can do it in excel with code I provided, just cant achieve same in access to a report page. as shown I have attached a text box that the data can be parsed to.

cheers.

[madpiet]

not sure how you expect to output pages of a report where there's (a) no data and (b) no table.

One option is to create a table of dates and outer join that to whatever your "events" table is. Then if you base a report on that, the Calendar date will always show up, whether it has child records or not.

[June7]

Okay, maybe I understand what you want.

In a loop that will open report for each day of month, pass date value to report with OpenArgs. Textbox in page header has expression: =[OpenArgs]

How will month and year be determined - user input?

Here is example assuming January 2025 is the month to report.

Sub DailyReport()
Dim d As Date, x As Integer
d = #1/1/2025#
For x = 1 To Day(DateSerial(Year(d), Month(d) + 1, 0))
    DoCmd.OpenReport "MyReport", acViewPreview, , , acDialog, Format(DateSerial(Year(d), Month(d), x), "dddd, mmm d yyyy")
Next
End Sub

If you prefer to send directly to printer, remove acViewPreview and acDialog parameters.

If you want to produce a single report with all days of month, that would probably require table approach suggested by madpiet. VBA could purge and populate a 'temp' table with appropriate date values or create a record for every date from first date in database to whenever in the future and leave them.

A query can actually dynamically produce a dataset of all dates in a given range but can perform slowly.

[savy]

Thanks, works a treat. yes will retrieve a variable user input for the month. Your solution is what I could not solve. Again, Thanks.