Re: Time over 24 hours

"Drum2001" <drum2001@xxxxxxxxx> wrote in
news:1141047864.477345.243400@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:

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.
.

Relevant Pages

  • Re: How do I calculate times over multiple records?
    ... I got the code to run but not with the SUM in place. ... doing a sum on the DateDiff Calculation. ... query) and therefore runs once for each row returned by the outer query. ... Excluding Weekends and Holidays is more complex. ...
    (microsoft.public.access.queries)
  • Re: How do I calculate times over multiple records?
    ... The sum statement was in the wrong place. ... doing a sum on the DateDiff Calculation. ... query) and therefore runs once for each row returned by the outer query. ... Excluding Weekends and Holidays is more complex. ...
    (microsoft.public.access.queries)
  • Re: Sum of calculated fields
    ... Since this seems to be in a report, ... property instead of using Sum. ... total overtime add a text box named txtRunOvertime with the ... Note that your calculation of midnight is overly complex. ...
    (microsoft.public.access.formscoding)
  • Re: Sum of calculated fields
    ... expressions I used so that it may make better sense, ... This database is used to calculate normal working hours and overtime hours, ... I have managed to get the sum() of Total hours to display in the report ...
    (microsoft.public.access.formscoding)
  • Re: writing formula for Access
    ... And then I Need to another formula to subtract $700 from the sum for a ... count your open parenthesis and subtract the close parenthesis in an ...
    (microsoft.public.access.gettingstarted)