Results 1 to 9 of 9

Thread: Timesheet help

  1. #1
    Join Date
    Oct 2007
    Posts
    21

    Unanswered: Timesheet help

    In a form I have:

    textbox1 for employee [Time in]
    textbox2 for employee [Time out]
    textbox3 for the [Total hours] worked

    To calculate the times, I have a formula in the control source of the total hours text box reading:

    =([Time out]-[Time in])*24

    With this formula I run into two problems.
    1. When the hours for time in and time out run over into the next day, I obviously get a negative number for the total hours
    2. I have a report with the employee's name and the sum of their [Total hours] worked set to a specific date range. It seems as though when having this formula to automatically total the hours worked, a "0" only appears on the table under the [Total Hours] field even though it will properly total the hours. With this happening, blank reports are being displayed (reading zeros for total time).

    Is there any other way to do this perhaps? I have tried After Update events which I cannot get to run properly due to my lack of knowledge in access. Any help would be greatly appreciated. Thanks.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Keep the date AND time in the timestamp field. Then, you can use the DateAdd and DateDiff functions.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Oct 2007
    Posts
    21
    I understand you can use date and time instead of just time. It worked with the function I used also, but is there another way? and can you give me an example of the datediff/dateadd? I'm not able to get them to work.

  4. #4
    Join Date
    Oct 2003
    Posts
    21
    When you get the negative number add 24 hours (1 day in whatever time unit you are using) to it, the result will be the elapsed time.

    Clock in at 8:00 PM, Clock out at 2:00 AM would result in -18 hours, add 24 and you get 6 hrs, the actual elapsed time.

  5. #5
    Join Date
    Oct 2007
    Posts
    21
    thanks, this is a lot of help... but the calculations are still just showing as zero on the table and showing as the calculated number on the form... do I have the function in the wrong place?

  6. #6
    Join Date
    Feb 2007
    Posts
    348
    That's because you are doing the calculation on the form only and not assigning it to any table fields (at least based on your first post). Which is a technique. If that is the route you want to go, you either need to create the same calculation in the Control Source of the report that you say it is missing from or you need to recreate it in the underlying query and make sure to add that field to your report.

  7. #7
    Join Date
    Oct 2007
    Posts
    21
    Quote Originally Posted by starkmann
    That's because you are doing the calculation on the form only and not assigning it to any table fields (at least based on your first post). Which is a technique. If that is the route you want to go, you either need to create the same calculation in the Control Source of the report that you say it is missing from or you need to recreate it in the underlying query and make sure to add that field to your report.
    The problem isn't in the report or query, its in the actual data table that its going to from the form.

    I am using a =DateAdd("h",24,([time out]-[time in])) in the control source of the [Total Hours]

    For some reason the calculation isn't going to the data table, it only appears on the form textbox.

    If I set the control source back to [Total Hours] and manually enter the number of hours into the box, it properly records the data to the table. I can only assume the problem is where I'm inputing the formula...

    Is there a way to leave [Total Hours] in the control source and input the function someplace else? I've tried putting =DateAdd("h",24,([time out]-[time in])) into an after update event, but it doesn't calculate anything.

  8. #8
    Join Date
    Feb 2007
    Posts
    348
    =DateAdd("h",24,([time out]-[time in]))
    This is your control source to the text box on the form correct?

    Your form doesn't know where to store it in tables. You either need an update query or you need to use a select query with the logic in it and have that feed the form. It won't update a table anywhere but it will hold the calculation for use on other forms and reports.

    Try uploading your app if I'm misunderstanding.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In this case, simply re-apply your fomula where needed.

    Ther is a long list of arguements for and against sotring calculated values, but in this instance I'd advise you simply re-calculate the values when needed and only store the core information.
    George
    Home | Blog

Posting Permissions

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