Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

MS Access 2003: Duration

Good Morning,

I am working on a duration field that calculates the elapsed time based on the start and end times displaying the results as “Days Hrs Mins”.  I have made it so that if the duration is less than 24 hours then the “Days” are not displayed.  However, I am having trouble replicating this for the “Hrs” as well.  If the duration is less than 60 minutes I would like “Hrs” not to be displayed.  Also, if the minutes equal zero then I would like minutes not to be displayed.  And lastly, if the duration results in only 1 minute or 1 hour I would like to leave the “s” off of “Hrs” and Mins”.  

Here is the code thus far.

IIF(DateDiff("n",[Start],[End]) < 1440, "", DateDiff("n",[Start],[End])\1440 & " Days ")
& (DateDiff("n",[Start],[End])\60) Mod 24 & " Hrs "
& Format(DateDiff("n",[Start],[End]) Mod 60,"00") & " Mins"

I know this seems a bit convoluted, but I would greatly appreciate any help.  

Thank you
0
cgraziano
Asked:
cgraziano
  • 3
  • 2
  • 2
2 Solutions
 
Dale FyeCommented:
How about a function:

Public Sub fnDuration(StartAt as variant, EndAt as Variant) as string

    Dim intDays as integer
    Dim intHours as integer
    Dim intMinutes as integer

    If isnull(StartAt) or isnull(EndAt) Then
        fnDuration = "invalid entry"
    else
        intMinutes = DateDiff("n", StartAt, EndAt)
        intDays = intMinutes\1440
        intMinutes = intMinutes - intDays * 1440
        intHours = intMinutes\60
        intMinutes = intMinutes - intHours * 60

        fnDuration = iif(intDays=0, NULL, intDays & " Days ") _
                          & iif(intHours = 0, "", intHours & " Hrs ") _
                          & iif(intMinutes = 0, "", intMinutes & " Mins:
    End If

End Sub
0
 
datAdrenalineCommented:
You will want to check out a "More Complete DateDiff Function" ...

http://www.accessmvp.com/djsteele/Diff2Dates.html

Examples:
? Diff2Dates("dhns", #08/01/2010 00:00:00#, #08/01/2010 00:00:02#)
2 seconds

? Diff2Dates("dhns", #08/01/2010 00:00:00#, #08/01/2010 00:00:01#)
1 second

? Diff2Dates("dhns", #08/01/2010 00:00:00#, #08/01/2010 00:01:01#)
1 minute 1 second

? Diff2Dates("dhns", #08/01/2010 00:00:00#, #08/01/2010 00:02:01#)
2 minutes 1 second

? Diff2Dates("dhns", #07/31/2010 00:00:00#, #08/01/2010 00:02:01#)
1 day 2 minutes 1 second
0
 
cgrazianoAuthor Commented:
Fyed,
Thank you for the reply.  I was able to modify your function a bit to make it work; however, I am having a little trouble identifying when eirther the hours or minutes equal 1.  When this happens I would like to have it to drop the "s" and display only "Hr" or "Min".

Here's what I have so far:

Public Sub fnDuration(StartAt as variant, EndAt as Variant) as string

    Dim intDays as integer
    Dim intHours as integer
    Dim intMinutes as integer

    If isnull(StartAt) or isnull(EndAt) Then
        fnDuration = "invalid entry"
    else
        intMinutes = Format(DateDiff("n",[Start],[End]) Mod 60,"00")
        intDays = DateDiff("n",[Start],[End])\1440
        intHours = DateDiff("n",[Start],[End])\60) Mod 24

        fnDuration = iif(intDays=0, NULL, intDays & " Days ") _
                          & iif(intHours = 0, "", intHours & " Hrs ") _
                          & iif(intMinutes = 0, "", intMinutes & " Mins)
    End If

End Sub

Thanks
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
datAdrenalineCommented:
The code I linked you to would be a direct copy and paste and provide the functionality you desire (dropping the "s" when appropriate, and dropping the unit when it is 0), you should give it a shot to see if it meets your needs.  You may have to modify the literal strings for the units, but that would be it.
0
 
Dale FyeCommented:
Try:

       fnDuration = iif(intDays=0, NULL, intDays & " Days ") _
                          & iif(intHours = 0, "", intHours & " Hr" & iif(intHours = 1, " ", "s ")) _
                          & iif(intMinutes = 0, "", intMinutes & " Min" & iif(intMinutes = 1, "", "s "))
0
 
cgrazianoAuthor Commented:
Fyed,

That did it.  Thanks a lot.  I appreciate the help.  

datAdrenaline,
Thanks for the info.  I will continue to reference that link.
0
 
datAdrenalineCommented:
Glad you have a solution that suits your needs!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now