Print reports base on 3 days each page in Access

I don't have MS Access with me right now, but do try look at this article:

Printing Two Records Per Page on a Microsoft Access Report
http://www.databasedev.co.uk/report_printing.html

try apply the same concept by changing the code to:

If [txtcounter] Mod 3 = 0 Then
        Me.Detail.ForceNewPage = 3
    Else
        Me.Detail.ForceNewPage = 0
    End If

@Ryan,
Thank you for response quick but your suggestion  is for the records not the date I'm look for how to show all the records on each page in every 3 days .  I had the answer from Jim Dettman for count how many records on each page, that works perfect.  but now I have to show all the records in 3 days on each page not how many records on each page.

thanks!

I group the Date, and try to use the same method as Jim Dettman suggested yesterday, and place code on the Date Header(as I group it), but it seems not work on the date.

I have a text box place on the Date Header and name it txtcountdate, control source set to =1, running sum set to No
here is my code:
if me.txtcountdate Mod 3 = 0 then
me.detail.forcenewpage = 2
else
me.detail.forcenewpage = 0
end if

it did not separate to new page after 3 days (3/17, 3/18, 3/19 all records should show on first page, then 3/20, 3/21, 3/22 reocrds should show on the second page, so on)

I just realized that should I use MOD for date field?  if not what should I use?

is there more than one record per day?

Jim.   

@Jim,
Yes, for example, 3/17 had 17 records, 3/18 had 6 records, and 3/19 had 12 records, so what I want to do is show all the records , 3/17 to 3/19 all the record on one page, then from 3/20 to 3/22 all the records show on another new page.  from 3/23 to 3/3/25 records show on anther page (force to a new page) etc.  in other word, every 3 days records show together, then the next 3 days records show to another new page (or force to a new page).  the records on each day is different
thanks

So on your grouping by date, add a footer if it's not already there.  Then modify the group's Interval. 




Set it to date and set the increment to 3.   Collapse the group footer if you don't need it down to a thin line.

Set the Group Footers "Force New Page" property to "After Section".

That should do it.

Jim.

@Jim,
sorry to keep asking, I found it but for some reason it shows each day on one page instead 3 days on each of print out.

On the design tab, you need to hit the Group and Sort Button:


  The options should then display at the bottom as I showed in the first screen shot.

that's what exactly I did, but it's weir that it force to a new page on each days instead 3 days even I set to 3 days.  just as a test, I was trying to do 1 week and it seems like it's ignoring.

You are grouping on the date field and have the interval set as a date for 3 days?

Jim.

@jim,
Yes, I did exactly like your screen shot above. also I notice the CTDATE(my date field) is sort correctly in the query but when I group in the report, it skips to show the next date randomly.  for example: 3/17, the 3/23, the 3/18, 3/19 and 3/20 did not show.  did I missing something?  I tried to add a Expr1 in the query named MonthYear: format([CTDATE],"mm/dd/yyyy"), and I group this MonthYear field in the report then it sort correctly and when I add the the "CTDATE" group and used your method, it's just show one date a page then force the next date to a new page instead every 3 days in a page.
I did search in the internet, but couldn't find anything about print records with days per section
Thank you for your patience with me

You didn't miss anything and it is working, but it's not producing results the way you want.   Take a look at this:



 It is placing dates in 3 day buckets, but the first bucket ends with 3/17.   It's not 3/17, 3/18, and 3/19, which is what you want.

 I think the best way to handle this is to modify the query.  What you'll need to do is come up with a "bucket number" and then group on that.  

 Give me a few minutes and I'll come up with something.

Jim.

OK, base the report on a query if it's not already and add a new column called BucketNum:

In the expression, replace #03/17/20# with a call to a function  (i.e. something like GetFirstDateValue())

Create that function in a standard module:

Public Function GetFirstDateValue () as Date
  ' Set this however you need to
  GetFirstDateValue   =
End Function

Then In the report, group on the bucketnum column, entire value.

I've got a meeting here in a few minutes, so I can't detail this out as much as I'd like, but if you get stuck holler.

Jim.

@Jim,
Sorry, I stuck on the function problem

How so?   Putting it in place or getting the date value?

Jim.

@jim,
having trouble on how to putting the function

Do you have the start and end dates on a form for running the report?   If so, the query can refer to the form field directly.

 If not, how/where do you get the starting date for the report?

Jim.

@Jim,
Let me try again and let you know.  Thanks!

@jim,
I don't need to create a function.  The only thing is how do I exclude the weekend in the Bucketnum, it seems it counts Monday to Wednesday, then it only count Thursday & Friday but did not count the coming Monday.  For example, if the date start 3/17 then it counts 3/17 to 3/19 these three days as 1, then 3/20 as 2, 3/23 to 3/25 as 3, 3/26 & 3/27 as 4, 3/30 & 3/31 as 5.  it should be count 3/17 to 3/19 as 1, 3/20 to 3/24 as 2, (coz 3/21 & 3/22 are weekend).  3/25 to 3/27 as 3

<<The only thing is how do I exclude the weekend in the Bucketnum >>

Are they supposed to be included in the report?   If so, what do you expect them to be grouped with?  And will your start date always be a specific day of the week or no?

Jim.      

the weekend is not suppose to include in the report, and the start date is always the week days but the counting days function counts first three days then instead count the next three day with one day false to the next week, it counts the remain two days the count the next tree days again and now I need to figure out how to have it count every three days despite if the start date begins on the first day of the week or mid day

Paste the following function into a module:

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

  Dim WholeWeeks As Variant
  Dim DateCnt As Variant
  Dim EndDays As Integer

  On Error GoTo Err_Work_Days

  BegDate = DateValue(BegDate)
  EndDate = DateValue(EndDate)

  WholeWeeks = DateDiff("w", BegDate, EndDate)
  DateCnt = DateAdd("ww", WholeWeeks, BegDate)
  EndDays = 0 

  Do While DateCnt <= EndDate
    If Format(DateCnt, "ddd") <> "Sun" And Format(DateCnt, "ddd") <> "Sat" Then
      EndDays = EndDays + 1
    End If 

    DateCnt = DateAdd("d", 1, DateCnt)
  Loop 

  Work_Days = WholeWeeks * 5 + EndDays

  Exit Function

  Err_Work_Days: 
    ' If either BegDate or EndDate is Null, return a zero
    ' to indicate that no workdays passed between the two dates.
    If Err.Number = 94 Then
      Work_Days = 0
      Exit Function
    Else
      ' If some other error occurs, provide a message.
      MsgBox "Error " & Err.Number & ": " & Err.Description
    End If

  End Function

Select allOpen in new window

Which was gotten from here:

https://docs.microsoft.com/en-us/office/vba/access/concepts/date-time/find-the-number-of-working-days-between-two-dates 

This does not take into account holidays.   If you want holidays, it must be done differently.

 In your query then, define BucketNum as:

BucketNum: Fix(WorkDays(<starting date value>,[CTDATE])/3)+1

Jim.

@jim,
sorry to keep bothering you and thanks for your patience with me.  attached is the screen shot for the query, i did add the function and change the query, but the BucketNum is still show some are correct and some are incorrect.
Untitled.png

No problem...I made a mistake i the formula.  Should be:

BucketNum: Fix( WorkDays(<starting date value>,[CTDATE]) - 1 / 3)+1

Jim.

@ jm,
Sorry,  I was trying to modified the query.  I should mention the screen shot I sent was close to what I was trying to do.  the only problem was on the bucketnum section, how do I have like:

ctdate     bucketnum
3/17/20         1
3/18/20         1
3/19/20         1
3/20/20         2
3/23/20         2
3/24/20         2
3/25/20         3
3/26/20         3
3/27/20         3
3/30/20         4
3/31/20         4
4/1/20           4
4/3/20           5
4/6/20           5
4/7/20           5
so on

 so I can use the bucketnum footer set to after section, then all the 1's (which is group by three days) will print together, and all the 2's (with three days) will print on next page.  sorry to confuse you.  your code almost works only when shows the bucketnum a little incorrect, some did how three days on one number but couple of them showed only two days on one number, of course the last one might be either two days or one day only.  
thanks for your time

OK, got it working here.   Expression in the query should be:

BucketNum: Fix((Work_Days(#03/17/20#,[EntryDate])-1)/3)+1

  I originally had it calling the function "WorkDays" (instead of "Work_Days") and added some ()'s to help the math.

I now get exactly what you show in your last comment.

Jim.

@jim,
thanks!  I modified the query to Bucketnum: Fix((Work_days([#3/17/20],[CourtDate])+1)/3, and don't know why few of the bucketnum  on only two days instead 3 day, like #5, #7, #10 others were correct, that's very strange.  attached is the screen shot.  I'm so sorry to occupy your time.  very appreciated your patience and helped!
ScreenShot.png

@jim,
Thanks again for your time and patience on my problem.  Thank you!

Thanks both, very appreciated!

Your welcome.

Jim