Results 1 to 3 of 3
  1. #1
    bhooper63 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2017
    Posts
    2

    Totaling the calculated result of subtract dates and times in a report

    I have a maintenance tracking database I'm building. I have created a report that will list the history of what work orders a technician has worked on. I have a calculated field where I subtract the stop time from the start time and then display the minutes the technician was working. When I group the results by date, I want to add all the minutes together and present a total time the technician was active for the day. However, when I try to total on the group and select the "Minutes" field it only gives me "Count Values" and Count Records", but will not allow "Sum". Below is the SQL I used for the report. The area in question is highlighted. Any ideas?



    SELECT tblEmployees.intEmployeeID, tblEmployees.txtEmployeeName, Year([tblWorkOrder]![dtDateTimeCreated]) & "-" & Format([tblWorkOrder]![lngSequence],"0000") AS [Work Order], tblMachineID.txtMachineID, Format([tblWorkData]![dtWorkStartDateTime],"mm/dd/yyyy hh:nn") AS [Start], tblWorkData.dtWorkStopDateTime, Format([tblWorkData]![dtWorkStopDateTime],"mm/dd/yyyy hh:nn") AS [Stop], Format(DateDiff("s",[tblWorkData]![dtWorkStartDateTime],[tblWorkData]![dtWorkStopDateTime])/60,"Fixed") AS Minutes, tblWorkData.txtWorkComments
    FROM (tblEmployees INNER JOIN tblWorkData ON tblEmployees.intEmployeeID = tblWorkData.lngTechID) INNER JOIN (tblMachineID INNER JOIN tblWorkOrder ON tblMachineID.anMachineID = tblWorkOrder.intMachineID) ON tblWorkData.lngWOSequence = tblWorkOrder.lngSequence
    WHERE (((tblWorkData.dtWorkStopDateTime) Is Not Null))
    ORDER BY tblEmployees.txtEmployeeName, Year([tblWorkOrder]![dtDateTimeCreated]) & "-" & Format([tblWorkOrder]![lngSequence],"0000");

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Format() and FormatNumber() functions always return a string value. Don't format in query. Do formatting in textbox.
    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
    bhooper63 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2017
    Posts
    2
    Perfect! Forgot about that. Thanks

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

Similar Threads

  1. Replies: 1
    Last Post: 07-10-2015, 01:58 PM
  2. Subtract 8 hours of work from result hours
    By alhareri in forum Queries
    Replies: 6
    Last Post: 12-02-2014, 08:25 AM
  3. Subtract a Calculated Field with a Query
    By NikkoF in forum Queries
    Replies: 12
    Last Post: 06-22-2014, 11:47 PM
  4. Totaling on a report
    By eskybel in forum Access
    Replies: 9
    Last Post: 04-02-2013, 01:11 PM
  5. How To Print Calculated Dates on Report
    By ChrisCione in forum Reports
    Replies: 2
    Last Post: 01-15-2011, 03:26 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