Solved

MS Access 2003: Duration

Posted on 2010-08-17
7
363 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

830 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