Dates within Reports (1 Viewer)

CHaskett

New member
Local time
Today, 09:18
Joined
Dec 15, 2020
Messages
5
I am not sure if this can even be done, but I have a report that lists equipment and serial numbers that a user selects from a form along with a start date and an projected end date. I currently have a VBA code that will list the dates from date issued to the Projected end date as long as the projected end date is not past 14 days. What I would like to happen is when the report is ran that the dates are listed from the date issued up to the projected end date and if the projected end date is more than 14 days create a second page with all the information as the first but continue to list all the dates until it reaches the projected end date. The current code that i have is as follows:
Code:
Private 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.Text1 = ColumnDate
        Case 2
          Me.Text2 = ColumnDate
        Case 3
          Me.Text3 = ColumnDate
        Case 4
          Me.Text4 = ColumnDate
        Case 5
          Me.Text5 = ColumnDate
        Case 6
          Me.Text6 = ColumnDate
        Case 7
          Me.Text7 = ColumnDate
        Case 8
          Me.Text8 = ColumnDate
        Case 9
          Me.Text9 = ColumnDate
        Case 10
          Me.Text10 = ColumnDate
        Case 11
          Me.Text11 = ColumnDate
        Case 12
          Me.Text12 = ColumnDate
        Case 13
          Me.Text13 = ColumnDate
        Case 14
          Me.Text14 = ColumnDate
      End Select
       
      ColumnDate = DateAdd("d", 1, ColumnDate)
      ColumnNmbr = ColumnNmbr + 1
    Wend
End Sub


This is what the report looks like. The dates list on the top row between serial and remarks.
1608033744799.png
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 06:18
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

That might be possible, but it would make things easier if you could post a sample copy of your db for trials.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:18
Joined
Feb 28, 2001
Messages
26,998
Normally, if you have a layout like this, you would have automatic generation of a second page if for some reason you had overflow data that required a second detail section. But because you are building a report that is not in Access's preferred (vertical) layout, you might have to trick it. Consider as a possibility that you always display the items as you do now BUT compute a REPEAT ENTRY of the items with some designator like "first overtime period" and REBOOK the items as though they were turned in and then checked out again for the next 14 days. Repeat this for as many overtime periods as you like. THEN make the report have a GROUP BY break based on the overtime period number (0 being "no overtime"). And there is no reason that you actually have to have a printing header that SAYS "first overtime" or whichever one it is.
 

CHaskett

New member
Local time
Today, 09:18
Joined
Dec 15, 2020
Messages
5
Hi. Welcome to AWF!

That might be possible, but it would make things easier if you could post a sample copy of your db for trials.
I have attached a copy of the DB for you to play around with.
 

Attachments

  • TEMPEST Database - Copy.zip
    3.5 MB · Views: 247

CHaskett

New member
Local time
Today, 09:18
Joined
Dec 15, 2020
Messages
5
Normally, if you have a layout like this, you would have automatic generation of a second page if for some reason you had overflow data that required a second detail section. But because you are building a report that is not in Access's preferred (vertical) layout, you might have to trick it. Consider as a possibility that you always display the items as you do now BUT compute a REPEAT ENTRY of the items with some designator like "first overtime period" and REBOOK the items as though they were turned in and then checked out again for the next 14 days. Repeat this for as many overtime periods as you like. THEN make the report have a GROUP BY break based on the overtime period number (0 being "no overtime"). And there is no reason that you actually have to have a printing header that SAYS "first overtime" or whichever one it is.
I am not sure I understand what you mean, are you saying to create a duplicate entry into the table of the same record?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:18
Joined
Feb 19, 2002
Messages
42,970
why would you not use a crosstab query to populate this report? It gets a little tricky if your headers change as these will. I've included a sample database that uses a temp table to pick up the column headings that will be shown on the report. The app produces x columns named 1,2,3,4, etc. up to about 20 which is all you can probably fit on an report in landscape view. The app also shows how to make an updatable form out of a normalized table look like a crosstab.
 

Attachments

  • BoundDenormalizedForm20201022.zip
    1.5 MB · Views: 247

CHaskett

New member
Local time
Today, 09:18
Joined
Dec 15, 2020
Messages
5
why would you not use a crosstab query to populate this report? It gets a little tricky if your headers change as these will. I've included a sample database that uses a temp table to pick up the column headings that will be shown on the report. The app produces x columns named 1,2,3,4, etc. up to about 20 which is all you can probably fit on an report in landscape view. The app also shows how to make an updatable form out of a normalized table look like a crosstab.
I appreciate the assistance but when trying to add more columns to the database that you have provided it does add records but does not display them if it is pass the predesignated number of columns on the report nor does it create a second page in order to view the added columns.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:18
Joined
Feb 19, 2002
Messages
42,970
I believe the example says the number of columns are fixed. Access reports cannot grow in width. The example shows how to take a FIXED, maximum number of columns and map variably named columns from a crosstab to the fixed names of the controls on the report.

If you want something that will grow in width, you can export the query to Excel.
 

CHaskett

New member
Local time
Today, 09:18
Joined
Dec 15, 2020
Messages
5
I believe the example says the number of columns are fixed. Access reports cannot grow in width. The example shows how to take a FIXED, maximum number of columns and map variably named columns from a crosstab to the fixed names of the controls on the report.

If you want something that will grow in width, you can export the query to Excel.
I am not wanting it to grow in width per say just when all the date text boxes are filled in create a second page to continue listing dates until the projected end date is reached.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:18
Joined
Feb 19, 2002
Messages
42,970
You might want to reread your last statement. You are asking it to grow in width even though you are saying that you are not. Export to Excel. My example won't work for you.
 

Users who are viewing this thread

Top Bottom