Total of hours worked per week

  • Thread starter Thread starter Del
  • Start date Start date
D

Del

Hello, and thanks in advance for your help.

I am working on a Time Sheet database for a friend. I am using Access
2000.

The database is setup as follows. For each day of the week there is a
Start Time and End Time field (Called Start Time1 - Start Time7 and
End Time1 - End Time7). The format of these fields is Short Time. I
also have an unbound text box associated with each day of the week
that is called Daily Hours. The total number of hours is automatically
calculated for each day. This part of the form works fine.

The problem is that I have another unbound text box called Weekly
Hours that is suppose to automatically total the Daily Hours. The
problem is that if there is a single day that does if blank then the
Weekly Hours is blank (Example: My friend works from 7 AM to 3PM
Monday - Friday, and 8AM to Noon on Saturday and not at all on Sunday
instead of the Weekly Hours reading 44 hours it is blank). I have
tried setting the Dialy Hours default value to 0 but that doesn't
help.

Any ideas??

Thanks again.
Del
 
Use Nz() around the expression that is null to solve the issue of no value
returned when something is blank.

The idea of 7 pairs of fields to store the start and end times is not a good
structure. Better to create a table with fields:
EmployeeID Number (long) Foreign key to Employee.EmployeeID
WorkDate Date/Time The date the person worked.
StartTime Date/Time The time the worker started.
EndTime Date/Time The date and time the worked finished.

Since all the shifts are in the one table, it is easy to break them into
weeks, or to ask how many hours someone worked in a month, or a bunch of
other questions. It also solves the question of summing records: the blank
records are just not there.

If you have shift workers working over night, you may prefer to combine the
date field and the 2 time fields into just:
StartDateTime
EndDateTime.
 
Back
Top