Prev: Quirky Query
Next: Expression for Criteria for Form with Combo Boxes
From: J_Goddard via AccessMonster.com on 30 Mar 2010 15:12
Joe -

I mis-typed the DateDiff - it should be

= datediff("n", [timeIn],[TimeOut])/60.0, using "n" to give you minutes. "m"
gives the difference in months.

Sorry 'bout that!!

John


joseph.johnson4 wrote:
>John,
>
>I utilized the expression:
>= datediff("m", [timeIn],[TimeOut])/60.0
>
>That you suggested. In the HoursWorked field it just displays 0:00. I
>checked the fields for TimeIn and TimeOut and they are both set to the
>Date/Time type. I know that the expression that you provided me is taking me
>in the right direction, but I am just not there yet. Any suggests. I do
>appreciate your helping me with this problem. I will also try paying around
>with the DateDiff function and see what I can come up with.
>
>Thanks,
>
>Joe
>
>> Hi -
>>
>[quoted text clipped - 62 lines]
>> >
>> >Thanks, Joe

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via http://www.accessmonster.com

From: joseph.johnson4 on 31 Mar 2010 12:05
John,

The subform is designed where it has a combo box that allows you to select
the Day of the Week, then a date field, then TimeIn field, then TimeOut
field, and lastly HoursWorked for the day. The TimeIN and TimeOut fields are
formatted to medium time. I changed the expression as you had instructed, but
it gives me really strange totals like: 3:01 PM TimeIn, 11:08 PM TimeOut =
2:48. I know in one of your posts you suggested changing the TimeIn and
TimeOut format to "dd mmm yyyy hh:nn" without the quotations. I tried with
the specified format, but still ended up with the same problem.

Joe

"J_Goddard via AccessMonster.com" wrote:

> Joe -
>
> I mis-typed the DateDiff - it should be
>
> = datediff("n", [timeIn],[TimeOut])/60.0, using "n" to give you minutes. "m"
> gives the difference in months.
>
> Sorry 'bout that!!
>
> John
>
>
> joseph.johnson4 wrote:
> >John,
> >
> >I utilized the expression:
> >= datediff("m", [timeIn],[TimeOut])/60.0
> >
> >That you suggested. In the HoursWorked field it just displays 0:00. I
> >checked the fields for TimeIn and TimeOut and they are both set to the
> >Date/Time type. I know that the expression that you provided me is taking me
> >in the right direction, but I am just not there yet. Any suggests. I do
> >appreciate your helping me with this problem. I will also try paying around
> >with the DateDiff function and see what I can come up with.
> >
> >Thanks,
> >
> >Joe
> >
> >> Hi -
> >>
> >[quoted text clipped - 62 lines]
> >> >
> >> >Thanks, Joe
>
> --
> John Goddard
> Ottawa, ON Canada
> jrgoddard at cyberus dot ca
>
> Message posted via http://www.accessmonster.com
>
> .
>
From: John W. Vinson on 31 Mar 2010 13:14
On Wed, 31 Mar 2010 09:05:01 -0700, joseph.johnson4
<josephjohnson4(a)discussions.microsoft.com> wrote:

>The subform is designed where it has a combo box that allows you to select
>the Day of the Week, then a date field, then TimeIn field, then TimeOut
>field, and lastly HoursWorked for the day. The TimeIN and TimeOut fields are
>formatted to medium time. I changed the expression as you had instructed, but
>it gives me really strange totals like: 3:01 PM TimeIn, 11:08 PM TimeOut =
>2:48. I know in one of your posts you suggested changing the TimeIn and
>TimeOut format to "dd mmm yyyy hh:nn" without the quotations. I tried with
>the specified format, but still ended up with the same problem.

Just note that the Format of a textbox or a field is *absolutely irrelevant*
to this problem.

A date is not stored as a text string, but as a Double Float count of days and
fractions of a day since midnight, December 30, 1899. A time-only date/time
field is just a time on that long-ago day - e.g. if you store 8:00am it's
actually STORED as 0.333333333333333333333, but can be displayed any way you
like - "8:00am" or "12/30/1899 08:00:00" or whatever.

The calculations work on the stored value, not on the format.
--

John W. Vinson [MVP]
From: joseph.johnson4 on 1 Apr 2010 21:09
John,

I was able to get the calculations that I needed for HoursWorked utilizing
the expression "= [TimeOut] - [TimeIn]" using the Short Tiem format.

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

In the end I will need to utilize the Sum of WeeklyHours and TotalHours to
calculate the gross weekly pay and YTD pay.

I have greatly appreciated your assistance and could use some more
suggestions or ideas.

Thanks, Joe

"John W. Vinson" wrote:

> On Wed, 31 Mar 2010 09:05:01 -0700, joseph.johnson4
> <josephjohnson4(a)discussions.microsoft.com> wrote:
>
> >The subform is designed where it has a combo box that allows you to select
> >the Day of the Week, then a date field, then TimeIn field, then TimeOut
> >field, and lastly HoursWorked for the day. The TimeIN and TimeOut fields are
> >formatted to medium time. I changed the expression as you had instructed, but
> >it gives me really strange totals like: 3:01 PM TimeIn, 11:08 PM TimeOut =
> >2:48. I know in one of your posts you suggested changing the TimeIn and
> >TimeOut format to "dd mmm yyyy hh:nn" without the quotations. I tried with
> >the specified format, but still ended up with the same problem.
>
> Just note that the Format of a textbox or a field is *absolutely irrelevant*
> to this problem.
>
> A date is not stored as a text string, but as a Double Float count of days and
> fractions of a day since midnight, December 30, 1899. A time-only date/time
> field is just a time on that long-ago day - e.g. if you store 8:00am it's
> actually STORED as 0.333333333333333333333, but can be displayed any way you
> like - "8:00am" or "12/30/1899 08:00:00" or whatever.
>
> The calculations work on the stored value, not on the format.
> --
>
> John W. Vinson [MVP]
> .
>
From: John W. Vinson on 3 Apr 2010 16:37
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]
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Quirky Query
Next: Expression for Criteria for Form with Combo Boxes