在24小时内添加时间 [英] Adding Time over 24 hours
问题描述
我有一张跟踪员工时间的表格。我有一个专栏
[HOURSWORKED]（日期/时间）。
用户通过表单输入他们完成任务所需的时间。
例如，03：45 = 3小时45分钟。
我正在运行以下查询：
SELECT Sum （Nz（[qryDateFilterTaskHours-Weekly]。[NumberOfCompletions]，0））
AS SumOfNumberOfCompletions，
格式（总和（Nz（[qryDateFilterTaskHours-Weekly]。[HoursWorked] ，0）），短
时间）AS SumOfHoursWorked
来自ALLTasksFilter LEFT JOIN [qryDateFilterTaskHours-Weekly] ON
ALLTasksFilter.ItemName = [qryDateFilterTaskHours-Weekly] .ItemName;
此查询旨在累计特定时间内的所有时间
期间。但是，当时间超过24小时时，它不会给正确的时间
。
有关我可以做些什么来建立这个的建议吗？
感谢您的帮助！
I have a table that tracks employee times. I have a column
[HOURSWORKED] (Date/Time).
Users, through a form, enter how long it takes them to complete a task.
For example, 03:45 = 3 hours and 45 mins.
I am running the following query:
SELECT Sum(Nz([qryDateFilterTaskHours-Weekly].[NumberOfCompletions],0))
AS SumOfNumberOfCompletions,
Format(Sum(Nz([qryDateFilterTaskHours-Weekly].[HoursWorked],0)),"Short
Time") AS SumOfHoursWorked
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTaskHours-Weekly] ON
ALLTasksFilter.ItemName = [qryDateFilterTaskHours-Weekly].ItemName;
This query is designed to total all the times within a specific time
period. However, when the time goes over 24 hours, it does not give
the correct time anymore.
Any suggestions on what I may do to acomplish this?
Thank you for your assistance!
推荐答案
看起来您正在添加实际时间时钟时间不是总时间。
显然如果你有6点并且加7点就可以获得
13:00（加7小时到6点） AM，你得到13:00或1 PM）。我想你需要的是什么
：
总和（小时（[TimeWorked]））+（总和（分钟（[TimeWorked]））\ 60）为你的工作时间
工作和总和（分钟（[TimeWorked]））Mod 60分钟工作。希望
有帮助！
It looks time you are adding actual clock times not total times.
Obviously if you have 6:00 and add 7:00 to it you are going to get
13:00 (add 7 hours to 6 AM and you get 13:00 or 1 PM). I think what
you need is something like this:
Sum(Hour([TimeWorked]))+(Sum(Minute([TimeWorked]))\60) for your hours
worked and Sum(Minute([TimeWorked])) Mod 60 for minutes worked. Hope
that helps!
问题是03:45是凌晨3:45，而不是3小时45分钟。你是处理经过时间的
，这不是一个实际的时间值。时间
值是一个时间点，例如2005年6月24日凌晨3:45。
要处理这个，你需要转换条目分钟（转换
所有内容到你用来简化添加的最小单位），在分钟上添加
，然后根据需要进行格式化。如果你想使用hh：nn
格式，那没关系。请记住，这只是一种格式，而不是
值。
这可以使用用户定义的函数或可能在查询中完成
本身。
示例（用户定义函数）：
总和（ConvToMinutes（Nz（CStr（[qryDateFilterTaskHours-Weekly]。 [HoursWorked]），0：00）））
作为SumOfHoursWorked
公共函数ConvToMinutes（strTime As String）As Long
Dim strArray（）As String
ReDim strArray（2）
strArray（）= Split（strInput，"："）
ConvToMinutes = CInt（strArray（0））* 60 + CInt（strArray（1））
结束功能
你可能是另一种可能性能够在没有用户的情况下进行查询
定义的函数（未经测试）：
总和（（Nz（左（CStr（[qryDateFilterTaskHours-Weekly]。[HoursWorked]，
Nz（Len（CStr（[qryDateFilterTaskHours-Weekly]。[HoursWorked]），0） - 3,0）* 60）
+ Nz（右（[qryDateFilterTaskHours -W eekly]。[工作时间]，2），0））
然后将结果格式化为小时：分钟，你可能想要在
$ b $中这样做b表单或报告显示答案而不是查询。
可能的控制来源：
= SumOfHoursWorked \ 60& "：" &安培; SumOfHoursWorked Mod 60
其他选项包括将字段设为两个字段，一个用于小时，一个用于
分钟。这可以节省分成几小时和几分钟来进行
计算（它已经拆分），但其余的都是相同的。你
仍会将小时数乘以60，然后将它们加到分钟。
-
Wayne Morgan
MS Access MVP
" Drum2001" <博士****** @ gmail.com>在消息中写道
news：11 ********************** @ j55g2000cwa.googlegr oups.com ...
The problem is that 03:45 is 3:45 AM, not 3 hours and 45 minutes. You are
dealing with an elapsed time, which is not an actual Time value. A Time
value is a point in time, such as 24 June 2005 at 3:45 AM.
To handle this, you need to convert the entry into minutes (convert
everything to the smallest unit you''re using to simplify the addition), add
up the minutes, then format it as desired. If you want to use the hh:nn
format, that''s fine. Just remember that it is only that, a format, not a
value.
This can be done using a user defined function or possibly in the query
itself.
Example (user defined function):
Sum(ConvToMinutes(Nz(CStr([qryDateFilterTaskHours-Weekly].[HoursWorked]),"0:00")))
AS SumOfHoursWorked
Public Function ConvToMinutes(strTime As String) As Long
Dim strArray() As String
ReDim strArray(2)
strArray() = Split(strInput, ":")
ConvToMinutes = CInt(strArray(0)) * 60 + CInt(strArray(1))
End Function
Another possibility that you may be able to do in the query without the user
defined function (untested):
Sum((Nz(Left(CStr([qryDateFilterTaskHours-Weekly].[HoursWorked],
Nz(Len(CStr([qryDateFilterTaskHours-Weekly].[HoursWorked]), 0) - 3, 0) * 60)
+ Nz(Right([qryDateFilterTaskHours-Weekly].[HoursWorked], 2), 0))
Then format the result back to hours:minutes, you may want to do this in the
form or report that displays the answer instead of in the query.
Possible Control Source:
=SumOfHoursWorked \ 60 & ":" & SumOfHoursWorked Mod 60
Other options include making the field two fields, one for hours and one for
minutes. This would save splitting it into hours and minutes to do the
calculation (it would already be split), but the rest would be the same. You
would still multiply the hours by 60 then add them to the minutes.
--
Wayne Morgan
MS Access MVP
"Drum2001" <dr******@gmail.com> wrote in message
news:11**********************@j55g2000cwa.googlegr oups.com...
我有一张跟踪员工时间的表格。我有一个专栏
[HOURSWORKED]（日期/时间）。
用户通过表单输入他们完成任务所需的时间。
例如，03：45 = 3我正在运行以下查询：
SELECT Sum（Nz（[qryDateFilterTaskHours-Weekly]。[NumberOfCompletions]，0））
AS SumOfNumberOfCompletions，
格式（Sum（Nz（[qryDateFilterTaskHours-Weekly]。[HoursWorked]，0）），Short
Time）AS SumOfHoursWorked
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTaskHours-每周] ON
ALLTasksFilter.ItemName = [qryDateFilterTaskHours-Weekly] .ItemName;
此查询旨在累计特定时间内的所有时间。但是，当时间超过24小时后，它不会再给出正确的时间了。
有什么建议我可以做些什么来完成这个？
感谢您的帮助！
I have a table that tracks employee times. I have a column
[HOURSWORKED] (Date/Time).
Users, through a form, enter how long it takes them to complete a task.
For example, 03:45 = 3 hours and 45 mins.
I am running the following query:
SELECT Sum(Nz([qryDateFilterTaskHours-Weekly].[NumberOfCompletions],0))
AS SumOfNumberOfCompletions,
Format(Sum(Nz([qryDateFilterTaskHours-Weekly].[HoursWorked],0)),"Short
Time") AS SumOfHoursWorked
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTaskHours-Weekly] ON
ALLTasksFilter.ItemName = [qryDateFilterTaskHours-Weekly].ItemName;
This query is designed to total all the times within a specific time
period. However, when the time goes over 24 hours, it does not give
the correct time anymore.
Any suggestions on what I may do to acomplish this?
Thank you for your assistance!
有一件事可能会有所帮助，我已经将信息记录为2
单独的列。 ...
[小时] [分钟]（两者都是）
添加这些内容会更容易，但仍然格式化为时间？
我能做些什么？
One thing that may help is I have the information already captured as 2
separate columns....
[Hours] [Mins] (Both Int)
Would it be easier to add these up, but still format as time?
What would I do exacty?
这篇关于在24小时内添加时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
