Prev: Quirky Query
Next: Expression for Criteria for Form with Combo Boxes
From: joseph.johnson4 on 5 Apr 2010 13:28
I utilized the HoursWorked: =DateDiff("n",[TimeIn],[TimeOut])/60 expression
as you had suggested and definitely see what you were talking about, in
regards, to utilizing the expression that I was using. The results were as
you had stated once I changed the formats to reflect a general number. Now I
have that aspect of the calculation taken care of on the TimeClock Subform
that I have created.

The problem that I am now experiencing is when it comes to the expression
for the WeeklyHoursTotal, which I have been utilizing the expression
=DSum([HoursWorked],"Schedule"), but all I keep getting in the box where the
total should appear is #Error. I have aslo tried the Sum fubction with the
same problem. I feel like I am overlooking something simple in order to get
it working, but I am just missing it.

I have appreciated your help and assistance with this particular problem.



"John W. Vinson" wrote:

> On Thu, 1 Apr 2010 18:09:01 -0700, joseph.johnson4
> <josephjohnson4(a)discussions.microsoft.com> wrote:
>
> >John,
> >
> >I was able to get the calculations that I needed for HoursWorked utilizing
> >the expression "= [TimeOut] - [TimeIn]" using the Short Tiem format.
>
> This will work... but ONLY for durations under 24 hours.
>
> >I have been working on trying to get the TimeClock Subform to sum the
> >HoursWorked per week utilizing the expression "= SUM(HoursWorked)". However,
> >I just keep getting a syntax error. I am also trying to create an expresiion
> >that keeps a running total of hours worked labelled TotalHours, but again I
> >am having the same syntax error when I utilize the expression "=
> >SUM(WeeklyHours)".
>
> Again, a Date/Time field is designed to store a specific point in time - NOT a
> duration. You should calculate the hours worked using the DateDiff function,
> to calculate minutes worked and divide by 60 (otherwise it will calculate the
> time in integer hours):
>
> HoursWorked: DateDiff("n", [TimeIn], [TimeOut])/60.
>
> This will get you a value such as 7.84 or 8.056, not an hours:minutes
> appearance, though you can use expressions to generate that appearance.
>
> >In the end I will need to utilize the Sum of WeeklyHours and TotalHours to
> >calculate the gross weekly pay and YTD pay.
>
> The hours and fractional hours will let you do this. A Date/Time value such as
> [TimeOut] - [TimeIn] will NOT let you do so.
>
> --
>
> John W. Vinson [MVP]
> .
>
From: John W. Vinson on 5 Apr 2010 22:38
On Mon, 5 Apr 2010 10:28:33 -0700, joseph.johnson4
<josephjohnson4(a)discussions.microsoft.com> wrote:

>The problem that I am now experiencing is when it comes to the expression
>for the WeeklyHoursTotal, which I have been utilizing the expression
>=DSum([HoursWorked],"Schedule"), but all I keep getting in the box where the
>total should appear is #Error. I have aslo tried the Sum fubction with the
>same problem. I feel like I am overlooking something simple in order to get
>it working, but I am just missing it.

You can't reuse a calculated field in a further calculation. You'll need to
recapitulate the calculation in the expression you're summing.
--

John W. Vinson [MVP]
First  |  Prev  | 
Pages: 1 2 3
Prev: Quirky Query
Next: Expression for Criteria for Form with Combo Boxes