访问2013年总和时间值 [英] Access 2013 Sum time values

问题描述

如何在Access 2013中对时间值求和?

How to make a sum of time values in Access 2013?

我的专栏看起来像这样:(感谢@HansUp用户！)

My column looks like this: (thanks to @HansUp user!)

SELECT t.[Time From],
       t.[Time Until],
       Format((t.[Time Until] - t.[Time From]), 'h:nn') AS [Total],
       Format(#08:00# - (t.[Time Until] - t.[Time From]), 'h:nn') AS [Missing]
FROM tblVolkan AS t;

如何在不出错的情况下求和?

How can I make a sum of Missing without getting Error?

推荐答案

您只需对时间跨度求和:

You just sum the time spans:

SELECT 
    Sum([Time Until] - [Time From]) AS [SumTotal],
    Sum(#08:00# - ([Time Until] - [Time From])) AS [SumMissing]
FROM 
    tblVolkan;

如果总计可以超过24小时，并且希望显示小时:分钟，请使用以下功能:

If totals can exceed 24 hours, and you wish an hour:minute display, use the function below:

SELECT 
    FormatHourMinute(Sum([Time Until] - [Time From])) AS [SumTotal],
    FormatHourMinute(Sum(#08:00# - [Time Until] - [Time From])) AS [SumMissing]
FROM 
    tblVolkan;

-

Public Function FormatHourMinute( _
  ByVal datTime As Date, _
  Optional ByVal strSeparator As String = ":") _
  As String

' Returns count of days, hours and minutes of datTime
' converted to hours and minutes as a formatted string
' with an optional choice of time separator.
'
' Example:
'   datTime: #10:03# + #20:01#
'   returns: 30:04
'
' 2005-02-05. Cactus Data ApS, CPH.

  Dim strHour       As String
  Dim strMinute     As String
  Dim strHourMinute As String

  strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
  ' Add leading zero to minute count when needed.
  strMinute = Right("0" & CStr(Minute(datTime)), 2)
  strHourMinute = strHour & strSeparator & strMinute

  FormatHourMinute = strHourMinute

End Function

这篇关于访问2013年总和时间值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！