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

    Time/Date

    Hi



    I have a table and a query that record time. they both work but when it comes to the report, I cant sum the total hours worked. I ill attach the query design, he query datasheet and the report for you to see.

    What am I missing? why can I not sum in the report as I have dome on many other reports.

    Please help

    Click image for larger version. 

Name:	Query Datasheet.jpg 
Views:	26 
Size:	54.0 KB 
ID:	31278

    Click image for larger version. 

Name:	Query.jpg 
Views:	25 
Size:	95.5 KB 
ID:	31279

    Click image for larger version. 

Name:	Report.jpg 
Views:	25 
Size:	98.7 KB 
ID:	31280

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Maybe it isn't a number field? Test it by creating this total in a totals query, then sum the total and see what Access gives you.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Access will sum textual numbers if they resemble text.
    You want to sum 5:16, 0:19 and 2:48? These look like ratios, so I'd be surprised if you summed hh:mm before using that method. Methinks another method would be required to sum hours and minutes if that's what you're trying to do.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Looks like another misunderstanding between Time and Duration.

    When you write a time, you write it as 5:16 AM/PM (with the colon).
    If you mean duration (how long something took), you would write 5 hours and 16 minuter or 5.26667 hours.

    The difference between 2 times, 7:30 (with the colon) and 12:46 (with the colon), is a duration of 5 hours and 16 minuter or 5.26667 hours.
    (if it is 7:30 AM and 12:46PM so the date change doesn't come into play)

    Depending on what you are wanting, you might use a UDF to calculate the duration, then sum the hours and minutes.

  5. #5
    Peter Simpson is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    83
    Hi Guys

    Thank you. Please can you explain to me what a UDF is and how I would use it in the calculation? Sorry for the confusion but I am new at access and even newer working with dates and times. I have done this exorcise before a few times with numbers and had no problem.

    You are correct in saying I want to calculate the duration as I need a log of hours worked so I can translate that into billable hours (ie 16.25Hours x R100.00 = R1625.00)

    Thanks for your patience guys

  6. #6
    Peter Simpson is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    83
    It was also suggested that I don't use separate date and time fields, I should rather make use of combined date/time fields. What are your thoughts? If you agree, what is the format that I use to represent a date and time in the same field (ie Thursday 15 November 2017 13:01PM)

  7. #7
    Peter Simpson is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    83
    Ok, so I have been playing around and have changed my time format to 21:01:00PM in both the StartTime field and the FinishTime field.

    I used the following formula in the query Difference: ([EndTime]-[StartTime])*24
    and it works to a degree.

    06:00:00PM (StartTime) and 10:00:00PM (FinishTime) my calculation offers a result of 4 (Hours) which is the duration of hour worked......100% happy with this. however the LoginDate and LogOutDate is the same.

    11:00:00PM (StartTime) with a loginDate of 14/11/2017 and 01:00:00AM (FinishTime) with LogOutDate of 15/11/2014 the calculation offers a result of -22 hours when the answer I am looking for is 2 hours.

    I seem to have a date / time issue. I have a feeling that this has to do with me setting up my table with separate date and time fields. How do I sort this out?

  8. #8
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I would agree there's no need to separate them. Date diff will give you the elapsed time according to the units you need. Suggest that this be minutes as it would be easy to calculate the time as decimal hours.
    A udf is a user defined function. That's where I was headed but was waiting to find out if I had correctly identified the goal.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Peter Simpson is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    83
    Sorry for the ignorance on my part, but for the life of me, I cant figure out how to get the date and time in the same field. Everything I have tried still results in the field input asking for just a date or just a time. That's my first problem that I need to solve.

  10. #10
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I cant figure out how to get the date and time in the same field.
    Not sure what you're trying to do with that. A date/time field will accept both and depending on the format, may look like 12/10/2015 7:05:44 AM for example. If all you do is enter the date, you'll only see 12/10/2015. If you enter the time part as well, you'll see something like the first example.
    Are you talking about dealing with old data that doesn't contain the time part now? In post 7 it seems like you're still dealing with "time only" fields because you're talking about subtracting them rather than using the DateDiff function as I suggested. When you review the link, note that I'm suggesting the interval parameter would be "n", and you'd probably divide that by 60 in your calculated control or field to get a fractional hour value.

    If you're really stuck, you can post a zipped copy of your db with instructions on how to replicate an issue.

  11. #11
    MiaAccess is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Mar 2011
    Location
    Texas
    Posts
    31
    I think you have to calculate in minutes and then convert back to hours and minutes. Below is what I use. It was given to me in another access forum a while back.
    MinutesWorked: DateDiff("n",[FromTime],([ToTime]))
    MinCharged: IIf([override]=0,0,[MinutesWorked])
    Time2: [Mincharged]\60 & Format([MinCharged] Mod 60,"\:00")

    This is my addition of hours on the report
    =Sum([Mincharged])\60 & Format(Sum([Mincharged]) Mod 60,"\:00")

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

Similar Threads

  1. Replies: 6
    Last Post: 04-19-2016, 03:58 PM
  2. Replies: 11
    Last Post: 07-20-2014, 06:22 PM
  3. Subtracting a date/time field from a number to get date/time
    By Lifeseeker1019 in forum Programming
    Replies: 4
    Last Post: 03-28-2014, 12:59 PM
  4. Replies: 1
    Last Post: 03-13-2014, 07:23 PM
  5. Replies: 6
    Last Post: 01-04-2011, 05:43 PM

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