Re: Query - Calulations

From: Michel Walsh (vanderghast_at_VirusAreFunnierThanSpam)
Date: 05/17/04 

Date: Mon, 17 May 2004 13:27:01 -0400

Hi,

            Nz( EndTime-StartTime, 8.0/24.0 )

returns the difference,unless one of the two fields is null, then, in that
case, it return 1/3 of a day.

        Nz( Nz( EndTime, Now) - StartTime, 8.0/24.0 )

return the running time, up to now, if endTime is null, but startTime is
known ( day in progress, we know there has been a start, but don't know the
exit time yet),
    endTime-StartTime if both are known,
    1/3 of a day if startTime is unknown (Null)

Pick your choice.

Hoping it may help,
Vanderghast, Access MVP

"BWilliam" <anonymous@discussions.microsoft.com> wrote in message
news:6CF65A31-BE50-4BB4-8FD3-2B39156ECFB3@microsoft.com...
> I have a small employee database that tracks time away from the
Office/Desk. When an employee is going to be away from the office all day or
away for meetings information is gathered by entering the employee ID, date,
reason code; and one of the following:
> If gone all day a checkmark (yes/no) is selected to reflect out all day.
> If gone for only part of the day the start and end time is entered (i.e.,
9:00 AM to 11:00 AM).
> I have two calculations that will determine the amount of time for each
field; Total Amount: [EndTime]-[StartTime] and All Day:
Switch(tblAttendance!OutAllDay=Yes,8). I am able to total these two fields
separately in a report but I'm not able to get a combined total.
> Additonal Informaiton: If "Out all Day" is not s elected the field is
null, If "Out all Day" is selected then the start and end time fields are
null.
>
> My Question is that I now need to add these to calculations together for a
grand total (per employee, per group, total for all), I've not been able to
come up with anything that works.

Relevant Pages

  • Re: Problem with using Format( ) function on 1.25
    ... >The 2 variables starttime and endtime take values from cells in the ... >of my sheet and repeats for every employee for every day of the week ... My guess is that there is something funny about the data in 'endtime'. ... Is that a computed value or do you enter it into the cell directly? ...
    (microsoft.public.excel)
  • Re: View challenge
    ... starttime and endtime indicates the first occurrence of the workshift. ... returns every occurrence of the workshifts for every employee, ... it appears a natural join on emp_schedule and workshift suffice for your view. ...
    (comp.databases.theory)
  • View challenge
    ... starttime and endtime indicates the first occurrence of the workshift. ... returns every occurrence of the workshifts for every employee, ...
    (comp.databases.theory)
  • RE: Add new tables to database
    ... If an employee can work in more than 1 area in the same day, then they can probably work in an area more than once in the same day. ... I'd remove tblAssignments.numAssignmentHours and replace it with columns for StartTime and EndTime. ... Jay ...
    (microsoft.public.access.tablesdbdesign)
  • RE: calculating difference between date/time fields
    ... Function BusinessHours(StartTime As Date, Endtime As Date) ... Dim DiffTime As Double ... If Endtime < StartTime Then ...
    (microsoft.public.excel.programming)