Solved

# MS Access 2003: Duration

Posted on 2010-08-17
373 Views
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
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
• 3
• 2
• 2

LVL 48

Accepted Solution

Dale Fye (Access MVP) earned 125 total points
ID: 33454730

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

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

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

LVL 11

Expert Comment

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

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

ID: 33458143
Fyed,

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

Thanks for the info.  I will continue to reference that link.
0

LVL 11

Expert Comment

ID: 33458316
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
###### Suggested Courses
Course of the Month2 days, 5 hours left to enroll