Adding Time
Drum2001
SELECT [DateFilter - Admin].Employee, [DateFilter - Admin].TimeTypeIn,
sectodur(IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>0,Sum(DateDiff("s",[clockin],[clockout]))-40*60*60,0))
AS Overtime, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1 AS
WeekOf
FROM [DateFilter - Admin]
GROUP BY [DateFilter - Admin].Employee, [DateFilter -
Admin].TimeTypeIn, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1
HAVING ((([DateFilter - Admin].TimeTypeIn)="Regular"));
This will come up with a recordset similar to below:
Employee TimeTypeIn Overtime WeekOf
B.Smith Regular 0:04:55 3/12/2006
B.Smith Regular 0:00:00 3/19/2006
Now, what I need to do is find the sum of the two Overtime Records
(0:04:55+0:00:00). This sum will then be put on a report. This can be
done within this query, another, or any other method if need be. Any
suggestions on how this may be completed? I Thank you for any
assistance!
~Drum2001
Bob Quintal
news:1143466631.7...@u72g2000cwu.googlegroups.com:
> I am running a query to calculate overtime:
>
> SELECT [DateFilter - Admin].Employee, [DateFilter -
> Admin].TimeTypeIn,
> sectodur(IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>
0
> ,Sum(DateDif苯("s",[clockin],[clockout]))-40*60*60,0))
>
> AS Overtime,
> DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1 AS WeekOf
> FROM [DateFilter - Admin]
> GROUP BY [DateFilter - Admin].Employee, [DateFilter -
> Admin].TimeTypeIn,
> DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1
>
> HAVING ((([DateFilter - Admin].TimeTypeIn)="Regular"));
>
>
> This will come up with a recordset similar to below:
>
>
> Employee TimeTypeIn Overtime WeekOf
> B.Smith Regular 0:04:55 3/12/2006
> B.Smith Regular 0:00:00 3/19/2006
>
>
> Now, what I need to do is find the sum of the two Overtime
> Records (0:04:55+0:00:00). This sum will then be put on a
> report. This can be
>
> done within this query, another, or any other method if need
> be. Any suggestions on how this may be completed? I Thank you
> for any assistance!
>
>
> ~Drum2001
If you are going to use the query as a recordsource for a report
or a form, I'd suggest you move the sec2dur call from the query
to the controls on the report, and just calculating the number
of seconds of time and overtime in the query. This will allow to
generate subtotals and totals as required.
--
Bob Quintal
PA is y I've altered my email address.
Drum2001
Thank you again for your help!.
Where in control would I put the sec2dur? Also, would I still use the
same full command,
sectodur(IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>0,Sum(DateDiff("s",[clockin],[clockout]))-40*60*60,0))
What would I need to put in the query to calculate just the seconds.?
Bob Quintal wrote:
> "Drum2001" <drum...@gmail.com> wrote in
> news:1143466631.7...@u72g2000cwu.googlegroups.com:
>
> > I am running a query to calculate overtime:
> >
> > SELECT [DateFilter - Admin].Employee, [DateFilter -
> > Admin].TimeTypeIn,
> > sectodur(IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>
> 0
> > ,Sum(DateDiff("s",[clockin],[clockout]))-40*60*60,0))
Bob Quintal
news:1143576361.9...@t31g2000cwb.googlegroups.com:
> Bob,
>
> Thank you again for your help!.
>
> Where in control would I put the sec2dur?
say the field in the query is called overtime. the controlsource
is [overtime]
change that to = sectodur([overtime])
Just make sure that the name of the textbox on the form or
report is named differently, because sometimes Access gets
confused about which (field or textbox) you mean if thae have
identical names.
Also, would I still
> use the same full command,
> sectodur(IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>
0
> ,Sum(DateDiff("s",[clockin],[clockout]))-40*60*60,0))
>
no, since the summing is still done in the query, you only need
to command sectodur([overtime])
>
> What would I need to put in the query to calculate just the
> seconds.?
>
IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>0,
Drum2001
After I put "IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>0,
Sum(DateDiff("s",[clockin],[clockout]))-40*60*60,0) " in my query,
and set the source to "sectodur([overtime])" in my report, I keep
getting errors.
It tells me There are too many ) in the query expression...
However, if I run the query, not through the report, it executes.
Bob Quintal
news:1146167414.2...@j33g2000cwa.googlegroups.com:
> Bob, I finally got around to tying this.
>
> After I put
> "IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>0,
> Sum(DateDif苯("s",[clockin],[clockout]))-40*60*60,0) " in my
> query, and set the source to "sectodur([overtime])" in my
> report, I keep getting errors.
>
> It tells me There are too many ) in the query expression...
>
> However, if I run the query, not through the report, it
> executes.
>
adding line breaks and indenting
IIf(
Sum(
DateDiff(
"s",[clockin],[clockout]
)
)-40*60*60>0,
Sum(
DateDif苯(
"s",[clockin],[clockout]
)
)-40*60*60,0
)
tells me that the number of ) is equal to the number of (
And it works in the query,
so there must be an extra ) somewhere in your report or in
sectodur().
you can check sectodur() the same way.
If you are using sectodur([overtime]) in the controlsource
property, you have to prefix it with = to indicate it is a
function.
HTH
Drum2001
It works now, however, I am getting the two records still, its not
summing.
This is my current query:
SELECT [DateFilter - Admin].Employee, [DateFilter - Admin].TimeTypeIn,
IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>0,Sum(DateDiff("s",[clockin],[clockout]))-40*60*60,0)
AS Overtime, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1 AS
WeekOf
FROM [DateFilter - Admin]
GROUP BY [DateFilter - Admin].Employee, [DateFilter -
Admin].TimeTypeIn, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1
HAVING ((([DateFilter - Admin].TimeTypeIn)="Regular"));
Any other suggestions? I really do appreciate your help!
Bob Quintal
news:1146585948.0...@j33g2000cwa.googlegroups.com:
I cannot see why that sql is returning two records, unless data
in one of the group by fields is not identical.