Dates change on Report

I have a database and table where the dates will change and need the report to reflect the different dates when the report is ran.  If I create a report with Titles once it seems would need to rebuild the report each time for the new dates.   Is there a way not to have to do this.  I am thinking to build a table of the dates and add text fileds to the report to refer to dates but I am also using a crosstab query with the date ranges and would need to line the columns up to the text fields.

May look like this one month with dates as headings

11/30/13                      12/31/13                   1/31/14
1111                                   5555                           abc

and this the next month

2/28/14                        3/40/14                        4/30/14

My question is whether I can build one report or if a new report will need to be designed each time.
Who is Participating?
chaauConnect With a Mentor Commented:
It is very easy to do. First of all, rename the Labels Name property for the Column Headings to Label1, Label2, Label3. Rename the TextBoxes Name property for the columns to Col1, Col2, Col3.
Then, create the event procedure for report's On Open event. Put this code into the procedure:
Private Sub Report_Open(Cancel As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tb_temp_Crosstab")
    Me.Label1.Caption = rs.Fields(2).Name
    Me.Label2.Caption = rs.Fields(3).Name
    Me.Label3.Caption = rs.Fields(4).Name
    Me.Col1.ControlSource = rs.Fields(2).Name
    Me.Col2.ControlSource = rs.Fields(3).Name
    Me.Col3.ControlSource = rs.Fields(4).Name
End Sub

Open in new window

I have attached the database where I have modified some dates
leezacAuthor Commented:
Thanks - that worked great.
leezacAuthor Commented:
Thanks - I am working on testing all the answers.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.