|
P: n/a
|
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!
| |
Share this Question
|
P: n/a
|
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... 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! | |
|
P: n/a
|
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)
| |
|
P: n/a
|
"Drum2001" <dr******@gmail.com> wrote in
news:11*********************@u72g2000cwu.googlegro ups.com: 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)
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. | |
|
P: n/a
|
What do I put where? If I put:
overtime = SecToDur(IIF(sum(datediff("s",[clockin],[clockout])-
40*60*60 >0, sum(datediff("s",[clockin],[clockout])-40*60*60,0))
In the query, it says "The Expression you entered has a function
containing the wrong number of arguments."
| |
|
P: n/a
|
"Drum2001" <dr******@gmail.com> wrote in
news:11**********************@i39g2000cwa.googlegr oups.com: What do I put where? If I put:
overtime = SecToDur(IIF(sum(datediff("s",[clockin],
[clockout])- 40*60*60 >0, sum(datediff("s",[clockin],[clockout])-40*60*
60,0))
In the query, it says "The Expression you entered has a
function containing the wrong number of arguments."
I parsed out your code, you are missing a parenthesis somewhere.
overtime =
SecToDur(
IIF(
sum(
datediff(
"s",[clockin],[clockout]
)
- 40*60*60 >0,
sum(
datediff(
"s",[clockin],[clockout]
)-40*60*60,0
)
)
1) in a query, you need a colon to define the column, instead of
an equals,
2) the nesting of parentheses can be a royal PITA. I oftem build
in notepad, tabbing in for each ( and out per ), to make sure
they're in the right places.
Overtime:
sectodur(
IIF(
sum(
datediff(
"s",[clockin],[clockout]
)
)-40*60*60 >0
,
sum(
datediff(
"s",[clockin],[clockout]
)
)-40*60-60
,
0
)
)
With this level of nested calls, I'd probably move the code to a
function.
--
Bob Quintal
PA is y I've altered my email address. | |
|
P: n/a
|
Thank you for your help again!
It looks like were closer. It seems as if it is subtracting 41 mins,
not 40 hours. Any suggestions?
With sample times entered, the queries come up with the following
results...
T
he Total Regular time = 51:59:31
The Total Overtime = 51:18:31
| |
|
P: n/a
|
"Drum2001" <dr******@gmail.com> wrote in
news:11**********************@p10g2000cwp.googlegr oups.com: Thank you for your help again!
It looks like were closer. It seems as if it is subtracting
41 mins, not 40 hours. Any suggestions?
With sample times entered, the queries come up with the
following results...
T
he Total Regular time = 51:59:31
The Total Overtime = 51:18:31
found this in the code I posted: )-40*60-60
which would give the exact 41 minutes instead of 40 hours error
you mentioned. It follows the second datediff. )-40*60*60 will
give the correct result. So sorry I can't type.
My employer keeps me on staff for my mental acuity, not my
physical dexterity.
--
Bob Quintal
PA is y I've altered my email address. | |
|
P: n/a
|
Bob,
Thank you very much for all your assistance!
That did work!
Bob Quintal wrote: "Drum2001" <dr******@gmail.com> wrote in
news:11**********************@p10g2000cwp.googlegr oups.com:
Thank you for your help again!
It looks like were closer. It seems as if it is subtracting
41 mins, not 40 hours. Any suggestions?
With sample times entered, the queries come up with the
following results...
T
he Total Regular time = 51:59:31
The Total Overtime = 51:18:31
found this in the code I posted: )-40*60-60
which would give the exact 41 minutes instead of 40 hours error
you mentioned. It follows the second datediff. )-40*60*60 will
give the correct result. So sorry I can't type.
My employer keeps me on staff for my mental acuity, not my
physical dexterity.
--
Bob Quintal
PA is y I've altered my email address. | |
|
P: n/a
|
Is there anyway to make it like the following. If there is overtime,
then have Regular Time display = 80:00:00
If under 80:00:00, then display the correct hours
Drum2001 wrote: Bob,
Thank you very much for all your assistance!
That did work!
Bob Quintal wrote: "Drum2001" <dr******@gmail.com> wrote in
news:11**********************@p10g2000cwp.googlegr oups.com:
Thank you for your help again!
It looks like were closer. It seems as if it is subtracting
41 mins, not 40 hours. Any suggestions?
With sample times entered, the queries come up with the
following results...
T
he Total Regular time = 51:59:31
The Total Overtime = 51:18:31
found this in the code I posted: )-40*60-60
which would give the exact 41 minutes instead of 40 hours error
you mentioned. It follows the second datediff. )-40*60*60 will
give the correct result. So sorry I can't type.
My employer keeps me on staff for my mental acuity, not my
physical dexterity.
--
Bob Quintal
PA is y I've altered my email address. | |
|
P: n/a
|
"Drum2001" <dr******@gmail.com> wrote in
news:11*********************@t31g2000cwb.googlegro ups.com: Is there anyway to make it like the following. If there is
overtime, then have Regular Time display = 80:00:00
If under 80:00:00, then display the correct hours
I could not get the original messages back via my ISP Google.
Anyway, It's just a matter of building up the calculations and
nesting them in IIF statements.
so if you have TotalTime (in seconds)
RegularTime is
iif(TotalTime > 80*60*60,80*60*60,Totaltime).
OverTime is
iif(TotalTime > 80*60*60,totaltime-80*60*60,0).
Those would go inside the SecToDur() function that I originally
provided in 2004
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
Drum2001 wrote: Bob,
Thank you very much for all your assistance!
That did work!
Bob Quintal wrote: > "Drum2001" <dr******@gmail.com> wrote in
> news:11**********************@p10g2000cwp.googlegr oups.com:
>
> > Thank you for your help again!
> >
> > It looks like were closer. It seems as if it is
> > subtracting 41 mins, not 40 hours. Any suggestions?
> >
> > With sample times entered, the queries come up with the
> > following results...
> > T
> > he Total Regular time = 51:59:31
> > The Total Overtime = 51:18:31
> >
> found this in the code I posted: )-40*60-60
> which would give the exact 41 minutes instead of 40 hours
> error you mentioned. It follows the second datediff.
> )-40*60*60 will give the correct result. So sorry I can't
> type.
>
> My employer keeps me on staff for my mental acuity, not my
> physical dexterity.
>
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.
--
Bob Quintal
PA is y I've altered my email address. | | This discussion thread is closed Replies have been disabled for this discussion. | |  Question stats - viewed: 2853
- replies: 10
- date asked: Feb 24 '06
|
