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
cgrazianoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.