时间超过24小时 [英] Time over 24 hours
问题描述
我设计了一个时间跟踪器数据库......基本上是一个时钟。我有
有报告计算每周每周工作的正常工作时数。
目前我正在运行以下查询：
-------------------------------------- --------------------------------
SELECT DateFilter.Employee，DateFilter.TimeTypeIn，
总和（[ClockOut] - [ClockIn]）AS Expr1
FROM DateFilter
GROUP BY DateFilter.Employee，DateFilter.TimeTypeIn
HAVING（（（DateFilter.TimeTypeIn）=" Regular"））;
ClockIn& ClockOut都是时间/日期
------------------------------------ ----------------------------------
一切都正确计算到24小时。在那之后，它变得混乱了。我希望总和能说出像38:25:02
任何建议都会受到赞赏！
I have designed a "Time Tracker Database"... Basically a Time Clock. I
have report that calculates the number of regular hours worked per
week.
Currently I am running the following query:
----------------------------------------------------------------------
SELECT DateFilter.Employee, DateFilter.TimeTypeIn,
Sum([ClockOut]-[ClockIn]) AS Expr1
FROM DateFilter
GROUP BY DateFilter.Employee, DateFilter.TimeTypeIn
HAVING (((DateFilter.TimeTypeIn)="Regular"));
ClockIn & ClockOut are both Time/Date
----------------------------------------------------------------------
Everything calculates correctly up to 24 hours. After that, it messes
up. I would like the sum to say something like 38:25:02
Any suggestions would be appreciated!
推荐答案
以您要报告的最小单位进行计算。例如，如果您需要小时和分钟，请在几分钟内完成计算。稍后格式化
获得总数后的方式。不要从ClockOut中减去ClockIn
，而是使用DateDiff函数并告诉它返回分钟（n）。
在你的例子中，看起来你想跟踪到第二个，是
是否正确？
示例：
TotalTime：Sum（DateDiff（" s"，[ ClockIn]，[ClockOut]））\ 3600&
格式（（Sum（DateDiff（" s"，[ClockIn]，[ClockOut]））Mod 3600）/ 86400，
"：nn：ss"）
立即窗口中的示例：
？10000 \ 3600&格式（（10000 Mod 3600）/ 86400，"：nn：ss"）
2:46:40
你正在运行的问题进入是你想要的经过时间。过去的
时间不是时间。数据类型，它只是一个数字。例如，下午2点到下午3点之间
的差异是1小时，而不是1点钟（1点）。那么你就好了想要把它格式化好像是时候了。
-
Wayne Morgan
MS Access MVP
" Drum2001" <博士****** @ gmail.com>在消息中写道
news：11 ********************** @ v46g2000cwv.googlegr oups.com ...
Do the calculation in the smallest units you want to report. For example, if
you want hours and minutes, do the calculation in minutes. Format it later
the way you want it after you get the total. Instead of subtracting ClockIn
from ClockOut, use the DateDiff function and tell it to return minutes (n).
In your example, it appears you''re wanting to track to the second, is that
correct?
Example:
TotalTime:Sum(DateDiff("s", [ClockIn], [ClockOut])) \ 3600 &
Format((Sum(DateDiff("s", [ClockIn], [ClockOut])) Mod 3600) / 86400,
":nn:ss")
Example from the Immediate window:
?10000 \ 3600 & Format((10000 Mod 3600) / 86400, ":nn:ss")
2:46:40
The problem you''re running into is that you want elapsed time. An elapsed
time isn''t a "time" data type, it is simply a number. For example, the
difference between 2pm and 3pm is 1 hour, not 1 o''clock (1:00). You are then
wanting it formatted as if it was a time.
--
Wayne Morgan
MS Access MVP
"Drum2001" <dr******@gmail.com> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...
我设计了一个时间跟踪数据库......基本上是一个时钟。我有报告计算每周工作的正常工作时数。
目前我正在运行以下查询：
---- -------------------------------------------------- ----------------
SELECT DateFilter.Employee，DateFilter.TimeTypeIn，
Sum（[ClockOut] - [ClockIn]）AS Expr1
FROM DateFilter
GROUP BY DateFilter.Employee，DateFilter.TimeTypeIn
HAVING（（（DateFilter.TimeTypeIn）=" Regular"））;
ClockIn& ClockOut都是时间/日期
---------------------------------------- ------------------------------
一切都能在24小时内正确计算。在那之后，它变得混乱起来。我希望总结能说出像38:25:02
任何建议都会受到赞赏！
I have designed a "Time Tracker Database"... Basically a Time Clock. I
have report that calculates the number of regular hours worked per
week.
Currently I am running the following query:
----------------------------------------------------------------------
SELECT DateFilter.Employee, DateFilter.TimeTypeIn,
Sum([ClockOut]-[ClockIn]) AS Expr1
FROM DateFilter
GROUP BY DateFilter.Employee, DateFilter.TimeTypeIn
HAVING (((DateFilter.TimeTypeIn)="Regular"));
ClockIn & ClockOut are both Time/Date
----------------------------------------------------------------------
Everything calculates correctly up to 24 hours. After that, it messes
up. I would like the sum to say something like 38:25:02
Any suggestions would be appreciated!
这很有效！谢谢！
现在这里有点麻烦......我怎么能这样做40以上的时间加价？b $ b小时加班？
基本上我将如何编码：TotalTime - 40:00:00 =（加班）
That works great!! thank you!
Now here is a little twist... How would I make it so anything over 40
hours would be overtime?
Basically how would I code: TotalTime - 40:00:00 = (overtime)
" Drum2001" <博士****** @ gmail.com>写在
新闻：11 ********************* @ u72g2000cwu.googlegro ups.com：
"Drum2001" <dr******@gmail.com> wrote in
news:11*********************@u72g2000cwu.googlegro ups.com:
这很棒!!谢谢！
现在这里有点麻烦......我怎么能这么做呢？超过40个小时会超时？
基本上我怎么样？代码：TotalTime - 40:00:00 =
（加班）
That works great!! thank you!
Now here is a little twist... How would I make it so anything
over 40 hours would be overtime?
Basically how would I code: TotalTime - 40:00:00 =
(overtime)
因为以秒为单位的总时间是总和（datediff（" ss"，[clockin]，
[clockout]）由Wayne给出，
加班时间为秒数
IIF（总和（datediff（" s，[clockin] ，[clockout]） - 40 * 60 * 60）> 0，总和
（datediff（" s"，[clockin]，[clockout]） - 40 * 60 * 60,0） ）
必须重新格式化，如Wayne所示。
加时赛=
IIF（总和（ datediff（s，[clockin]，[clockout]） - 40 * 60 * 60> 0，总和
（datediff（" s"，[clockin]，[clockout]） -40 * 60 * 60,0）/ 3600&
格式（（IIF（sum（datediff（" s"，[clockin]，[clockout]） - 40 * 60 * 60> ; 0，
sum（datediff（" s"，[clockin]，[clockout]） - 40 * 60 * 60,0）Mod 3600）/
86400 ，：nn：ss&q uot;）
必须以单行的形式进入查询，非常麻烦，我确定我是遗漏了一些（）所以请使用下面用户定义的
函数。
超时= SecToDur（IIF（sum（datediff（" s，[clockin] ，[clockout]） -
40 * 60 * 60> 0，sum（datediff（" s"，[clockin]，[clockout]） - 40 * 60 * 60,0））
公共功能sec2dur（秒长）作为字符串
错误继续下一步
Dim hrs As Long
Dim mins As Integer
Dim secs as Integer
hrs = Int（秒/ 3600）
分钟= Int（（秒 - （3600 *小时））/ 60）
秒=秒 - （小时* 3600 +分钟* 60）
sec2dur =格式（小时，＃，## 0"）_
& "：" &安培;格式（分钟，00）& "：" _
&格式（秒，00）
结束功能
-
Bob Quintal
PA是我改变了我的电子邮件地址。
since the total time in seconds is sum(datediff("ss",[clockin],
[clockout]) as given by wayne,
Overtime in seconds is
IIF(sum(datediff("s",[clockin],[clockout])-40*60*60) >0, sum
(datediff("s",[clockin],[clockout])-40*60*60,0))
and that has to be reformatted as Wayne showed, .
Overtime =
IIF(sum(datediff("s",[clockin],[clockout])-40*60*60 >0, sum
(datediff("s",[clockin],[clockout])-40*60*60,0)/3600 &
Format((IIF(sum(datediff("s",[clockin],[clockout])-40*60*60 >0,
sum(datediff("s",[clockin],[clockout])-40*60*60,0) Mod 3600) /
86400, ":nn:ss")
which has to go into the query as a single line, is quite a
mess, and I''m sure I''m missing some () so use the User defined
function below.
overtime = SecToDur(IIF(sum(datediff("s",[clockin],[clockout])-
40*60*60 >0, sum(datediff("s",[clockin],[clockout])-40*60*60,0))
Public Function sec2dur(seconds As Long) As String
On Error Resume Next
Dim hrs As Long
Dim mins As Integer
Dim secs As Integer
hrs = Int(seconds / 3600)
mins = Int((seconds - (3600 * hrs)) / 60)
secs = seconds - (hrs * 3600 + mins * 60)
sec2dur = Format(hrs, "#,##0") _
& ":" & Format(mins, "00") & ":" _
& Format(secs, "00")
End Function
--
Bob Quintal
PA is y I''ve altered my email address.
这篇关于时间超过24小时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
