Solved

MS Access 2003: Duration

Posted on 2010-08-17
7
337 Views
Last Modified: 2013-11-28
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
Comment
Question by:cgraziano
  • 3
  • 2
  • 2
7 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 125 total points
ID: 33454730
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
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 33454927
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
 

Author Comment

by:cgraziano
ID: 33456867
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 11

Expert Comment

by:datAdrenaline
ID: 33457588
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 125 total points
ID: 33457723
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
 

Author Closing Comment

by:cgraziano
ID: 33458143
Fyed,

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

datAdrenaline,
Thanks for the info.  I will continue to reference that link.
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 33458316
Glad you have a solution that suits your needs!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now