Link to home
Create AccountLog in
Avatar of urjudo
urjudoFlag for United States of America

asked on

Print reports base on 3 days each page in Access

Hi Experts,
How do I report a report that only show 3 days record on each page.  for example, my report date are from 3/17/2020 to 5/15/2020.  I would like the report print first page is from 3/17 to 3/19 then the second page is print from 3/20 to 3/22 so on. each page only have 3 days of the records.

Thanks
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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
Avatar of urjudo

ASKER

@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!
Avatar of urjudo

ASKER

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)
Avatar of urjudo

ASKER

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.   
Avatar of urjudo

ASKER

@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. 

User generated image


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.
Avatar of urjudo

ASKER

@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:

User generated image
  The options should then display at the bottom as I showed in the first screen shot.
Avatar of urjudo

ASKER

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.
Avatar of urjudo

ASKER

@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:

User generated image

 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:
User generated image
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.

Avatar of urjudo

ASKER

@Jim,
Sorry, I stuck on the function problem
How so?   Putting it in place or getting the date value?

Jim.
Avatar of urjudo

ASKER

@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.
Avatar of urjudo

ASKER

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

ASKER

@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.      
Avatar of urjudo

ASKER

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

Open 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.


Avatar of urjudo

ASKER

@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.
Avatar of urjudo

ASKER

@ 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.
Avatar of urjudo

ASKER

@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
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of urjudo

ASKER

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

Thanks both, very appreciated!
Your welcome.

Jim