Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27
  1. #16
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I found a couple of items that I changed.



    I found that if you entered 1 record with 24 hrs and 0 hours, 24 was passed to the function. It should have been 60 minutes. I modified the function call at the bottom of the form. I also added the NZ() function in case a control was NULL.

    And I modified the UDF. Now if you enter just 24 hours, the string 001:00:00 is returned.

  2. #17
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    That function aint going to work. Wasp1 needs to look at the business rules and start over again. I don't know how they ended up with hours and minutes in a table like that but maybe there is a way to intercept the data in a more raw format. If not, combining the two to then separate them out again does not seem like a practical approach.

    I created the following behind a form

    Code:
    Dim dblHours As Double
    Dim dblMinutes As Double
    Dim dblAnswer As Double
    
    dblHours = Me.txtFirst.Value
    dblMinutes = Me.txtSecond.Value
    
    'dblAnswer = (dblHours / 60) + dblAnswer
    dblAnswer = dblHours * 60 + dblAnswer
    
    MsgBox ActualTimeWorked(dblAnswer)
    When I input the following I got what I would expect from the formula, a wrong answer.
    txtFirst = 50 Hours
    txtSecond = 20 minutes
    Result = 002:02:00

    I tried applying the formula from the query and got another, expected, bad result. Maybe someone can shed some light on the subject and explain to me how this thing is supposed to work but, I don't see it as functional or practical.

  3. #18
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Now I feel like a dummy. I downloaded Steve's example and looked at the form he edited. I noticed that it was working and that the function did not look much different from the one I was using. Long story short... I noticed I was adding the wrong Double variable in my VBA.

    The only thing is, I still do not see how the remainder is calculated as minutes still, but I guess there is not the conversion happening that I thought was occurring.

  4. #19
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    OP wants to display the elapsed time as days:hrs:min

    I get 002:02:20.

    50 hours and 20 minutes = 3020 minutes = 2 days 2 hours 20 minutes, i.e. 002:02:20

    Does Steve's version work with the large numbers?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #20
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by June7 View Post
    OP wants to display the elapsed time as days:hrs:min...
    Yah, I made a tired mistake. Steve's solution works. No Overflow either.

  6. #21
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe a better way is to pass both the hours and minutes, then do the calculations.????? Maybe won't have the overflow problem??

  7. #22
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Steve, the sample function you provided in post 16 did not Overflow when I tested it with <60 seconds. I tested it up to 99X days by using like 20000 hrs. FWIW...

  8. #23
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Yep. Using another variable for the minutes seems to make a difference.

    However, I still get overflow in the Immediate Window with 1603 * 60.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #24
    Wasp1's Avatar
    Wasp1 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    21
    Ok...The function is working correctly....


    Code:
    Public Function ActualTimeWorked(mins As Long) As String
    Dim dd As Long, hh As Long, nn As Long
    
    dd = mins \ 1440
    hh = (mins - (dd * 1440)) \ 60
    nn = mins Mod 60
    ActualTimeWorked = Format(dd, "000") & " " & "Days" & ":" & " " & Format(hh, "00") & " " & "Hrs" & " & " & Format(nn, "00") & " " & "Min"
    
    End Function

    I changed mins from As Double to As Long and changed mm to nn as a buddy of mine pointed out.

    The next step, I need to do is to address years in min.


    Code:
    Public Function ActualTimeWorked(mins As Long) As String
    Dim yy As Long, dd As Long, hh As Long, nn As Long
    
    yy = mins * 60
    dd = mins \ 1440
    hh = (mins - (dd * 1440)) \ 60
    nn = mins Mod 60
    ActualTimeWorked = Format(dd, "000") & " " & "Days" & ":" & " " & Format(hh, "00") & " " & "Hrs" & " & " & Format(nn, "00") & " " & "Min"
    
    End Function
    This is not working:

    I show 60 minutes in an hour. 24 hours in a day. 365 days in a year. So:
    60 times 24= 1440 (1440 minutes in a day.)
    1440 times 365= 525600 (525600 minutes in a year.)
    unless its a leap yr.

    Need assistance on getting the year figured out.

    Wasp1

  10. #25
    Wasp1's Avatar
    Wasp1 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    21
    Ok.....Here is what I have and the code is throwing me to a COMPILE error:

    Code:
    ActualTimeWorked = Format (yy, "0") & ":" & (dd, "000") & " " & "Days" & ":" & " " & Format(hh, "00") & " " & "Hrs" & " & " & Format(nn, "00") & " " & "Min"

    Code:
    Public Function ActualTimeWorked(mins As Long) As String
    Dim yy As Long, dd As Long, hh As Long, nn As Long
    yy = mins - (1440 * 365)
    dd = mins \ 1440
    hh = (mins - (dd * 1440)) \ 60
    nn = mins Mod 60
    ActualTimeWorked = Format (yy, "0") & ":" & (dd, "000") & " " & "Days" & ":" & " " & Format(hh, "00") & " " & "Hrs" & " & " & Format(nn, "00") & " " & "Min"
    
    End Function

  11. #26
    Wasp1's Avatar
    Wasp1 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    21
    My bad........I fixed the compile error......now receiving overflow error on

    Code:
    yy = mins - (1440 * 365)

    Revised Code: Still getting error:

    Code:
    Public Function ActualTimeWorked(mins As Long) As String
    Dim yy As Long, dd As Long, hh As Long, nn As Long
    yy = mins - (1440 * 365)
    dd = mins \ 1440
    hh = (mins - (dd * 1440)) \ 60
    nn = mins Mod 60
    ActualTimeWorked = Format(yy, "000") & ":" & Format(dd, "000") & " " & "Days" & ":" & " " & Format(hh, "00") & " " & "Hrs" & " & " & Format(nn, "00") & " " & "Min"
    
    End Function

  12. #27
    Wasp1's Avatar
    Wasp1 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    21
    Here is the solution to my issue:


    Code:
    Public Function ActualTimeWorked(mins As Long) As String
    Dim yy As Long, dd As Long, hh As Long, nn As Long
    
    yy = mins \ 525600
    dd = (mins - (yy * 525600)) \ 1440
    hh = (mins - (dd * 1440) - (yy * 525600)) \ 60
    nn = mins Mod 60
    
    ActualTimeWorked = Format(yy, "00") & " " & "Yrs" & ":" & Format(dd, "000") & " " & "Days" & ":" & " " & Format(hh, "00") & " " & "Hrs" & " & " & Format(nn, "00") & " " & "Min"
    
    'End Function

    I had much help on this as my own calculations were way off......I learned something NEW today.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-11-2014, 11:57 PM
  2. Replies: 2
    Last Post: 11-04-2011, 02:45 AM
  3. Replies: 3
    Last Post: 04-10-2010, 10:22 AM
  4. Convert null to "" in Access
    By isaac_2004 in forum Access
    Replies: 1
    Last Post: 12-04-2009, 06:50 PM
  5. Replies: 21
    Last Post: 06-03-2009, 05:54 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