Want to total all grouped hours into total sum of hours in an access report

Posted on 2012-09-11
Last Modified: 2012-09-18

I need assistance again please.  

I received assistance with obtaining total hours based on another field being grouped.  I have tried to sum up all of the total hours listed, but am not able to successfully sum up all of the total hours collected.

This worked in the query and shows well in the report:

TotalHours: FormatHourMinute(Sum([YourDateField]))

I have tried to use the sum formula and making sure the the format of the field in the report footer would have the format of short time but using sum and the field give me either the first value in the list, or #Error in the field, or Data type mismatch in criteria expression.

The latest tried was:


The other fields used were easy to total in the Report footer.  The total hours field just allows for Count Records and Count Values.

Thank you so much.
Question by:mtrout
    LVL 30

    Expert Comment

    If you have a field name in report's record source, f1
    In Field group add text field with control source = SUM(f1)

    For grand total, use the same t field name in report's record source, f1
    In report footer add a text box with record source = SUM(f1)

    In your code:
     Total in report footer:
    =Sum([TotalHours])      ====>  Sum([YourDateField])

    Author Comment

    That's what I put in the Report footer


    and I get the error
    'Data type mismatch in criteria expresson'

    Maybe I have it in the wrong place or something?

    Unfortunately, I will not be able to respond until the 25th.  I hope it's ok to resume this later.  I have enclosed what I am doing.

    I did not want to 'look bad' by not responding; and without a reason why.  Will converse again soon.  Thank you so much.
    LVL 30

    Accepted Solution

    The problem is with last control, it sums date format.

    Try this version:

    Author Closing Comment

    When the query was modified by Hnasr to include the correct total hours of the sum and the format was added to the report, this worked.  Thank you Hnasr!
    LVL 30

    Expert Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    This collection of functions covers all the normal rounding methods of just about any numeric value.
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now