Overnight Calculation QUeries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is my SQL I am trying to create in a query. THe objective is to find
the time an employee start work and end work. the problem lies when they
start work very late like 11pm and end work the next day at 9am or 7am or
10am. THe result I got is not correct. Could anyone help me with this?

UPDATE TimeCard2 SET TimeCard2.OT_HRS = IIf([OverNight]=Yes,("#" &
(CDbl([DateEntered])+1) & [END_WORK] & "#"-"#" & [DateEntered] & [SCHD_START]
& "#"),[END_WORK]-[SCHD_START])
WHERE (((TimeCard2.SCHD_START) Is Not Null) AND ((TimeCard2.BEG_WORK) Is Not
Null) AND ((TimeCard2.END_WORK) Is Not Null) AND ((TimeCard2.BASE_HRS) Is Not
Null));
 
You could try this

UPDATE TimeCard2 SET TimeCard2.OT_HRS = IIf([OverNight]=Yes,("#" &
DateAdd("d",1,[DateEntered]) & " " & [END_WORK] & "#" - "#" & [DateEntered]
& " " & [SCHD_START] ,[END_WORK]-[SCHD_START])
WHERE (((TimeCard2.SCHD_START) Is Not Null) AND ((TimeCard2.BEG_WORK) Is Not
Null) AND ((TimeCard2.END_WORK) Is Not Null) AND ((TimeCard2.BASE_HRS) Is Not
Null));

- Raoul
 
Here is my SQL I am trying to create in a query. THe objective is to find
the time an employee start work and end work. the problem lies when they
start work very late like 11pm and end work the next day at 9am or 7am or
10am. THe result I got is not correct. Could anyone help me with this?

UPDATE TimeCard2 SET TimeCard2.OT_HRS = IIf([OverNight]=Yes,("#" &
(CDbl([DateEntered])+1) & [END_WORK] & "#"-"#" & [DateEntered] & [SCHD_START]
& "#"),[END_WORK]-[SCHD_START])
WHERE (((TimeCard2.SCHD_START) Is Not Null) AND ((TimeCard2.BEG_WORK) Is Not
Null) AND ((TimeCard2.END_WORK) Is Not Null) AND ((TimeCard2.BASE_HRS) Is Not
Null));
Click to expand...

You may really want to consider storing the date and time *in the same
field*. An Access Date/Time field is stored as a Double Float count of
days and fractions of a day (times) since an arbitrary start point.
Secondly, you should *NOT* store durations - such as OT_HRS - in a
date time field; in fact I'd recommend not storing them *at all* since
the duration can be calculated as needed.

If you must store the date and the start/end times in separate fields,
you can use the DateDiff function to calculate the number of minutes
worked (and divide by 60 to get hours and fractions of an hour. I
don't understand the interaction between DateEntered and date worked;
what I'd recommend is simply storing the date and time the employee
started work in [BEG_WORK] and the date and time they left in
[END_WORK], doing away with the OverNight field altogether, and using

DateDiff("n", [BEG_WORK], [END_WORK])

to calculate how many minutes they worked.

John W. Vinson[MVP]
 
Back
Top