Gary Kjos
garykjos at gmail.com
Tue Dec 5 08:37:40 CST 2006
Doing math stuff on times is often tricky Doris. Even for old timers like me.
Are the Time In and Time Out fields defined as date/dime fields? If
they are I think you would be better off calculating your hours value
using the DateDiff function as Andy recommended. It has the format;
-----------------------------------------------------------------------------------------------
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
-----------------------------------------------------------------------------------------------
according to my Access97 help file.
And the interval code for hours is "h".
so in the query you would have
---------------------------------------------------------
Hours:DateDiff("h",[TimeIn],[TimeOut])
---------------------------------------------------------
You may need to reverse the TimeIn and TimeOut fields. I usually get
them in the wrong order the first time I do it.
I'm guessing that what might be happening in your current process is
that when you get over 24 hours you are actually getting a number of
days and a number of hours and you are perhaps only displaying the
result in a format that shows hours and so are not seeing the number
of days. By switching to use the DateDif to calculate a number of
hours at a detail level your sumation will always be in hours with no
date value or format getting in the way of your desired result.
Doing math on date/time type fields is problematic as access always
stores a date with the time even if you don't display it. If you don't
specify a date it will default to the beginning date in Access Time
which is December 30, 1899. So then when you start doing math on the
date/time values, the results have dates in them as well as the times
you are expecting. Access date/time fields are intended to represent a
POINT IN TIME and not a QUANTITY OF TIME. So when you subtract one
from the other you need to get the result expressed in a non-date/time
type of field.
Hope this helps and I didn't totally confuse you. Good luck getting it
to work. Sounds like you are close.
GK
On 12/4/06, DorisH3 at aol.com <DorisH3 at aol.com> wrote:
> I have a table that maintains the Volunteer Number, Time In, Time Out, Date
> Worked. I have put together a report using a query that shows the volunteers
> name, volunteer number, date worked, time in, time out. On the report I
> show all of the fields that are contained in the query along with a calculation
> TimeOut - TimeIn which I named Hours. My report looks like this
>
> Name Vol# Date TimeIn TimeOut
> Hours
> Alice White 1099
> 10/1/06 4:00 PM 7:00 PM
> 3
> 10/5/06 4:00 PM 7:00 PM
> 3
>
> 10/7/06 4:00 PM 7:00 PM 3
> 10/10/06 4:00 PM 7:00 PM
> 3
>
> In the group footer I did a calculation as follows =Sum([Hours]) and in the
> report footer I did the same.
>
> It appears that anything under 24 hours calculates OK, anything over 24
> hours is incorrect....can anyone help me...I am a novice at this so I apologize
> ahead of time.
>
> Doris
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
Gary Kjos
garykjos at gmail.com