Problem with Time portions of the day

[nadav.katz]

Hello,

I am facing the following problem....I am an access newbie:


I have to make a form which has the following controls - DayHours,
NightHours, TotalHours.


Day shifts are defined as shifts from 7am until 6pm
Night shifts are defined as shifts from 6pm until 7am


I have a table with EmployeeID, StartTime, EndTime.


As an example, if exmployee X worked from 1pm until 10pm - that
should
count as 6 day work hours and 3 night work hours.


How could I implemet such a system that works out the day and night
work hours based on the shift times???


Many thanks in advance,


Nadav

 

[S.Clark]

In order to do "Time Math" across a two day period, then you'll need to enter
the date and time for each entry. e.g. 7pm to 6am needs to reflect like
#6/5/08 7pm# and #6/6/08 6am#

To calculate a difference in time, you would use the DateDiff function, and
specify to return the Hours parameter. See the Help file for the gory
details. Practice in the Immediate Window (Ctrl + G). The factor above in
which you need to include the date with the time is going to cause a little
pain, but you can work around it using the DateSerial function.

The way I would do it is three queries. The first would calc all employee
Day hours, and append them to a temp table. The second would calc all the
Night hours, and append them to the same temp table. Then you could do a
final sum based on the EmployeeID as needed.

Familiarize yourself with Append Queries, and the functions DateDiff &
DateSerial and you should be all set.

 

[KARL DEWEY]

Try this --
SELECT NaDav.EmployeeID, NaDav.StartTime, NaDav.EndTime,
IIf([StartTime]-DateValue([StartTime])<#12/30/1899 18:0:0#,Format(#12/30/1899
18:0:0#-([StartTime]-DateValue([StartTime])),"h:nn"),0) AS [Day Hours],
Format(IIf([StartTime]-DateValue([StartTime])>=#12/30/1899
18:0:0#,([EndTime]-[StartTime]),[EndTime]-(DateValue([StartTime])+#12/30/1899
18:0:0#)),"h:nn") AS [Night Hours]
FROM NaDav;

Test data ---
EmployeeID StartTime EndTime
1 6/10/2008 1:00:00 PM 6/10/2008 10:00:00 PM
2 6/10/2008 7:00:00 PM 7/10/2008 2:00:00 AM
3 6/10/2008 6:30:00 PM 6/10/2008 7:15:00 PM

Query results ---
EmployeeID StartTime EndTime Day Hours Night Hours
1 6/10/2008 1:00:00 PM 6/10/2008 10:00:00 PM 5:00 4:00
2 6/10/2008 7:00:00 PM 7/10/2008 2:00:00 AM 0 7:00
3 6/10/2008 6:30:00 PM 6/10/2008 7:15:00 PM 0 0:45