Results 1 to 3 of 3
  1. #1
    Peter Simpson is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    83

    Timesheet problem

    I Have a Timesheet problem.



    Table is :

    arpsDesignLogTbl with the following fields

    DesignLogID_Pk (AutoNumber)
    EmployeeID_Fk (Number)
    LoginDate (Date/Time)
    LogOutDate (Date/Time)
    StartTime (Date/Time)
    FinishTime (Date/Time)

    the relationships, forms and queries all populate correctly. My issue is calculating the difference between the StartTime and FinishTime to give me a result of X hours

    EG: (StartTime) 12:50:00PM - (FinishTime) 13:09:00PM = 12:19:00AM when the result should be 19 minutes


    Please help with the correct calculation and the best place to insert it (Query or the Report)

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,831
    you should not have separated date and time. (1 field holds both)
    you want 2 fields StartTime and EndTime. Both are date/time. 1/1/17 12:01 pm, 1/1/17 3:00 pm.
    This allows you to calculate elapsed time.
    Tho,
    with your design, you can still add the 2 fields together to get 1 timestamp :
    vStart = [LoginDate] & " " & [StartTime]
    vEnd = [LogOutDate ] & " " & [FinishTime]

    elapsed = DateDiff("h",vStart,vEnd)





  3. #3
    ssanfu is online now Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,289
    You would use in a query:
    Code:
    EG: DateDiff("n",[LoginDate] + [StartTime],[LogOutDate] + [FinishTime])
    or
    Code:
    EG: DateDiff("n",[LoginDate] & " " & [StartTime],[LogOutDate] & " " & [FinishTime])
    or
    Code:
    EG: DateDiff("n",CDate([LoginDate] & " " & [StartTime]),CDate([LogOutDate] & " " & [FinishTime]))

    in a report (in a text box control), maybe:
    Code:
    = DateDiff("n",[LoginDate] + [StartTime],[LogOutDate] + [FinishTime])
    Look up the syntax of the function "DateDiff()" in HELP.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need help in timesheet application
    By shod90 in forum Forms
    Replies: 7
    Last Post: 01-04-2016, 11:05 AM
  2. Full App - Mileage/Timesheet Example
    By pkstormy in forum Sample Databases
    Replies: 9
    Last Post: 05-27-2015, 03:37 PM
  3. Timesheet problem
    By vincentsp in forum Access
    Replies: 1
    Last Post: 01-27-2015, 11:50 AM
  4. Design a timesheet application
    By Trevi in forum Database Design
    Replies: 1
    Last Post: 11-04-2010, 11:25 AM
  5. Timesheet template
    By meso in forum Access
    Replies: 0
    Last Post: 07-27-2009, 05:28 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums