Solved

MS Access 2003: Duration

Posted on 2010-08-17
7
379 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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 48

Assisted Solution

by:Dale Fye
Dale Fye 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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

624 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