# Thread: Trying to add total hours worked each day, to then Weekly Total Hours Worked?,URGENT!

1. Registered User
Join Date
Feb 2012
Posts
9

## Unanswered: Trying to add total hours worked each day, to then Weekly Total Hours Worked?,URGENT!

I need Serious help , as it is part of my coursework for A level ICT, and i have been trying to figure it out for hours but no luck.

Im trying to workout the weekly total hours worked. by adding up all the sub total hours, but when a employee has a day off it doesn't seem to work out the Total weekly hours and stays blank. it only works out the total weekly hours when the employees are working on all of the days.

This is my database.

New Compressed (zipped) Folder.zip

in the staff form
on Staff ID 3.

Wednesday data has not been inputted.
and all the other days have,
and teh weekly hours worked is blank.

how can i fix this?

2. Moderator
Join Date
Mar 2009
Posts
5,442
Try using the Nz() function to convert Null values into zero before summing. Null values propagate when used in an arithmetic operation, e.g. Null + 5 = Null. See: Microsoft Access tips: Common Errors with Null

3. Registered User
Join Date
Feb 2012
Posts
9
Is there any chance you could do it for me,
you seem to have a lot of knowledge on Microsoft access. i would really much appreciate it, if you could help me on this. as my deadline is under a week away .

4. Registered User
Join Date
Feb 2012
Posts
9
Originally Posted by Sinndho
Try using the Nz() function to convert Null values into zero before summing. Null values propagate when used in an arithmetic operation, e.g. Null + 5 = Null. See: Microsoft Access tips: Common Errors with Null
Is there any chance you could do it for me,
you seem to have a lot of knowledge on Microsoft access. i would really much appreciate it, if you could help me on this. as my deadline is under a week away .

5. Moderator
Join Date
Mar 2009
Posts
5,442
Did you at least try the solution I provided?

6. Registered User
Join Date
Feb 2012
Posts
9
Originally Posted by Sinndho
Did you at least try the solution I provided?
I have tried using the Nz function, in the Query.
but when i run it, there is no difference, not sure what im doing wrong.
is there any chance you could have a go in solving it.

7. Moderator
Join Date
Mar 2009
Posts
5,442
I can at least have a look. Please post the SQL of the query you're trying to use.

8. Registered User
Join Date
Feb 2012
Posts
9
Originally Posted by Sinndho
I can at least have a look. Please post the SQL of the query you're trying to use.
I really Appreciate it, ive spent days trying to figure it out, but no Look
here's the database which is attached in the folder.

New Compressed (zipped) Folder.zip

9. Moderator
Join Date
Mar 2009
Posts
5,442
When I'm at work I cannot download files from unknown sites, sorry.

10. Registered User
Join Date
Jan 2012
Posts
98

May I say that greater self satisfaction & improved knowledge will be taken from problems solved using your own perseverance & initiative. The generous support you receive comes from individuals who give their time freely and simply asking them to 'do it for you' can occasionally make them less inclined to provide that support.

Dave

11. Registered User
Join Date
Feb 2012
Posts
9
Originally Posted by Sinndho
When I'm at work I cannot download files from unknown sites, sorry.
When would you be able to Look at it? how about i send you it by Email?

This is particular expression i have used

Total hours worked on Monday:
DateDiff("n",[Start Time (Mon)],[End Time (Mon)])/60

if no start time or end time has been entered, how can i make it as Null
i am using medium time. eg. 07:00PM

12. Registered User
Join Date
Feb 2012
Posts
9
Originally Posted by dave0810471

May I say that greater self satisfaction & improved knowledge will be taken from problems solved using your own perseverance & initiative. The generous support you receive comes from individuals who give their time freely and simply asking them to 'do it for you' can occasionally make them less inclined to provide that support.

Dave
I understand what you are saying, and of course Knowledge can be improved on by solving certain aspects individually with perseverance, but i have been so persistent in figuring out how to solve this problem. that's why joining a database forum was the last resort.

13. Registered User
Join Date
Feb 2012
Posts
9
But thanks Dave.

I really Appreciate your time and effort so Thank you

14. Registered User
Join Date
Jan 2012
Posts
98
If you wanted to use the Nz() function in the Date Diff then:
Total hours worked on Monday: DateDiff("n",Nz([Start Time (Mon)],0),Nz([End Time (Mon)]),0)/60

If you want to display the hours worked for each day then you would need to use the function in each field that calculates hours worked per day. If not then you can just use the Nz() function when summing the hours for the week:
Weekly Hours Worked: Nz([Total hours worked on Sunday],0)+Nz([Total hours worked on saturday],0)+...etc

Hope this helps,
Dave

15. Registered User
Join Date
Feb 2012
Posts
9
Yup, i found this extremely helpful Dave so thank You.
kind of annoyed at my self because i was using the Nz function, but was applying it in the wrong way.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•