Results 1 to 7 of 7
  1. #1
    jplatman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2014
    Posts
    4

    Cool Check if the total of a group of detail records is under a threshold value and not print the subrepo

    Hi,

    I have a report that has many many subreports. The subreport is using grouping in the query to create the subreport with detail. I was asked to exclude subreports where the total for that subreport is below a threshold. Let's say $100. The information for the total is in a footer text box. Instead of using the query that I have that creates a subreport even when the total is under $100, I have created a query that calculates the total of the groupings without including the extra information I would need in the detail and I have used that as a join to the data query that I need in the detail and then used that query in my subreport. This does seem to work but I would have to create an additional query for each subreport. I would think there should be an easier way to do this. In the end I would like to know what the total is before formatting the footer on the report so I can determine if I even want to print that report. (Maybe I can cancel the whole report when I do get to the footer, I haven't done that person). Any assistance would be appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't think you can get away without the additional queries.

    Not printing a subreport under certain conditions has been discussed in other threads. I encountered this issue and my solution was to have a label with caption "No data for this item" display when the subreport had no records. I went this route because even though there were no records, the subreport control still occupied space and because of forced page break, I still got a page and didn't want the page to be blank.

    Might be of interest https://www.accessforums.net/reports...ata-18885.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jplatman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2014
    Posts
    4
    Thanks for your response. There is data in the subreport, I just don't want it to print the report if the total of the data is lower than a threshold amount. For your situation you can put in logic to turn visible to false when there is no data in your subreport. That way you don't have an empty page. If the subreport can shrink and you hide the page break that is on the main form then you won't have the problem with the coding.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Have you tested your threshold validation code for hiding subreport yet?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    jplatman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2014
    Posts
    4
    It works with the additional query, which I will need to put on each subreport. When there is no data because the additional query reduces the detail to return no detail lines then the subreport is hidden and the page break in the main report is hidden, thereby avoiding any extra blank pages.

  6. #6
    jplatman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2014
    Posts
    4
    I just wanted a easier solution.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The only other possibility I can think of is domain aggregate (DSum, DCount, etc) expression instead of the queries.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Display and print group records in one page only?
    By wittybrent in forum Reports
    Replies: 1
    Last Post: 05-11-2013, 09:25 AM
  2. Replies: 7
    Last Post: 05-06-2013, 02:39 PM
  3. Counting Detail Records by Group
    By Paul H in forum Reports
    Replies: 7
    Last Post: 10-21-2011, 02:53 PM
  4. Print records by group
    By lizzywu in forum Reports
    Replies: 1
    Last Post: 10-20-2011, 10:31 AM
  5. columns for group detail but not group header?
    By Coolpapabell in forum Reports
    Replies: 0
    Last Post: 08-21-2009, 08:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums