Results 1 to 15 of 15
  1. #1
    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.

    Can someone please help me with this.

    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. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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
    Have a nice day!

  3. #3
    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. #4
    Join Date
    Feb 2012
    Posts
    9
    Quote Originally Posted by Sinndho View Post
    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. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Did you at least try the solution I provided?
    Have a nice day!

  6. #6
    Join Date
    Feb 2012
    Posts
    9
    Quote Originally Posted by Sinndho View Post
    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. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I can at least have a look. Please post the SQL of the query you're trying to use.
    Have a nice day!

  8. #8
    Join Date
    Feb 2012
    Posts
    9
    Quote Originally Posted by Sinndho View Post
    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. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When I'm at work I cannot download files from unknown sites, sorry.
    Have a nice day!

  10. #10
    Join Date
    Jan 2012
    Posts
    97
    Attached is your DB - Sinndho's advice works perfectly.

    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
    Attached Files Attached Files

  11. #11
    Join Date
    Feb 2012
    Posts
    9
    Quote Originally Posted by Sinndho View Post
    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. #12
    Join Date
    Feb 2012
    Posts
    9
    Quote Originally Posted by dave0810471 View Post
    Attached is your DB - Sinndho's advice works perfectly.

    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. #13
    Join Date
    Feb 2012
    Posts
    9
    But thanks Dave.

    I really Appreciate your time and effort so Thank you

  14. #14
    Join Date
    Jan 2012
    Posts
    97
    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. #15
    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
  •