Results 1 to 6 of 6
  1. #1
    JohnSmith is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    7

    Count "Date Ranged" Records From SubReport Place Value On Main Report

    Starting from the form "frmAccidentMenu", user is going to enter a date range.


    The report "rptAccidents" will produce results according to that date range.
    A subreport will be list all the CarOwners involved.

    Issue:
    Report "rptAccidents" does not count the number of CarOwners within the date range.

    I have 2 tables and a query for each table.

    Should I create a single query for both tables?

    Ranged Records File.zip

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    your button raises an error - it cannot find form frmmainmenu.txtbxDateEnd so I guess you didn't test before zipping. It's fixable though.

    Your table design is wrong but that has already been pointed out to you. Owners should be in accidents, not the other way around. Every time an owner has an accident, you'll have to repeat all their details (fields).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your report label states that the count should be 120 and that's the number I see so I don't understand what the issue is - at least not once the query is fixed as per my first post.

    Click image for larger version. 

Name:	120.jpg 
Views:	14 
Size:	13.6 KB 
ID:	43660
    EDIT - while I'm at it, I don't see the need for a subreport either.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    See if this helps. Modified query to look at correct form for end date (as Micron has also stated) and changed query to Totals query and added the count field.
    Then in the report, modified the control source to include dates and changed the Dcount to DSum to sum the count from the query.

    The Total Car Owners Involved in Accidents will sum according to the dates in frmAccidentMenu.

    Click image for larger version. 

Name:	query.png 
Views:	15 
Size:	49.1 KB 
ID:	43661

    And here's the report:

    Click image for larger version. 

Name:	form.png 
Views:	15 
Size:	53.9 KB 
ID:	43662

    EDIT: The control source can actually be shortened to:
    =DSum("CountOfOwnerID","qsAccidents")

    because the date criteria in the query qsAccidents is sufficient.
    Last edited by davegri; 12-14-2020 at 11:32 PM. Reason: added note at bottom

  5. #5
    JohnSmith is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    7
    Thank for the descriptive instructions breaking it down "barney-style".
    I can't say thank you enough!











    Thank You!
    Click image for larger version. 

Name:	Swell Guy.jpg 
Views:	9 
Size:	260.5 KB 
ID:	43670

  6. #6
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    That's a pretty big THANKS! Good luck with the project.

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

Similar Threads

  1. Replies: 7
    Last Post: 12-14-2020, 07:48 PM
  2. Replies: 2
    Last Post: 01-23-2017, 07:06 PM
  3. Replies: 4
    Last Post: 12-20-2015, 12:16 PM
  4. Replies: 3
    Last Post: 04-02-2015, 08:08 AM
  5. Replies: 11
    Last Post: 05-09-2014, 12:00 PM

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