List Dates in Report from Start to Finish

[CHaskett]

I am using MS Access 2007-2016
I have a report that receives its information from a form, what I am trying to make happen is when the report is loaded it lists the date issued in the first text box and list all consecutive dates until it reaches the projected end date. I have been able to do this but if my projected date is longer than 14 days the report will not create a second page to continue until it has reached the projected end date. Below is a copy of the VBA code that I currently have:

 Sub Report_Load()
 
    Dim ColumnDate As Date
    Dim ColumnNmbr As Integer
 
    ColumnDate = [Date_Issued]
    ColumnNmbr = 1
 
    While ColumnDate <= Me.Projected_End_Date
 
      Select Case ColumnNmbr
        Case 1
          Me.Text148 = ColumnDate
        Case 2
          Me.Text150 = ColumnDate
        Case 3
          Me.Text151 = ColumnDate
        Case 4
          Me.Text152 = ColumnDate
        Case 5
          Me.Text153 = ColumnDate
        Case 6
          Me.Text154 = ColumnDate
        Case 7
          Me.Text155 = ColumnDate
        Case 8
          Me.Text301 = ColumnDate
        Case 9
          Me.Text302 = ColumnDate
        Case 10
          Me.Text303 = ColumnDate
        Case 11
          Me.Text304 = ColumnDate
        Case 12
          Me.Text305 = ColumnDate
        Case 13
          Me.Text306 = ColumnDate
        Case 14
          Me.Text307 = ColumnDate
      End Select
 
      ColumnDate = DateAdd("d", 1, ColumnDate)
      ColumnNmbr = ColumnNmbr + 1
    Wend
End Sub
 

[twinnyfo]

CHaskett,

Welcome to Bytes!

It seems like your Report is designed fundamentally incorrectly.

You should have your dates listed and the Report simply lists those dates sequentially--no matter how many (few) dates you have.

What does the Table/Query behind this report look like?

Standing by to hepp!

[CHaskett]

twinnyfo,
The Query for the record source for the report is as follows:

 SELECT * FROM tblEquipLoanRecords WHERE [Tracking#] = ''; 

The table in which the report draws information from only has the date issued and the projected end date listed in it. I some how need to create a VBA code that will take the two dates in the table make a list of sequential dates in between the two dates, and then display all the dates within individual textboxes on the report. The dates will go into the top row of text boxes while the other row of text boxes will hold other information from the table. I am able to squeeze 14 textboxes in the top row, hence why if my projected end date is further out than 14 days the current VBA code will not list any date past the 14th day from the date issued. I need it to start with date issued and list every day between until the projected end date is reached then stop. I have tried Loops and If statements but i can never get the report to create a second page past the 14th textbox in the top row.

[twinnyfo]

Sorry for my delay in response.....

1. First, it would be helpful for us to see a visual of what you have in mind. I think I kind of "get it." but a picture sometimes hepps. Maybe you can post either a pic of your current report (as displayed--so we know what types of information you are displaying)?

2. I do have some ideas on how to make such a query, but your description--if I understand it correctly--limits yourself to the size of the reporting capabilities in Access and is not data related. For example, a query can have as many as 255 output fields, but I don't know any report that could possibly fit 255 fields wide on any one sheet of paper (and Access reports are limited to 22x22 inches.

3. Along those lines, what is the maximum possible number of dates you would ever deal with? This is actually where you might want to start. If you are talking about a year's worth of dates, then Access is incapable to performing such a task. However, if your output generally has 14 or fewer dates and one in one thousand records has 15, and fewer than that has 16 (and son on), then we must ask the utility of covering all possible outcomes.

Obviously, I can't answer such questions, so I toss it back to you for response.