[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS Access 2000 -  Subtracting End Date from Start Date

Posted on 2006-03-24
7
Medium Priority
?
1,256 Views
Last Modified: 2008-02-01
I have a report with StartDate and EndDate fields on a report. I have another field on the report where I am want to subtract the StartDate from the EndDate. The format on the StartDate and EndDate fields is General Date Format.

I want to be able to subtract one from the other to get elapsed time. I tried DateDiff but I cannot get the detail I need ( Hours Minutes) from this function.

I would appreciate any insight.

Lou
0
Comment
Question by:Lou Dufresne
7 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 400 total points
ID: 16286613
This will give you a string output detailing the time elapsed:

Function timediff(d1 As Date, d2 As Date) As String
    Dim mdiff, hdiff As Integer
    mdiff = Abs(DateDiff("n", d1, d2))                                    ' Total Time elapsed in minutes    
    hdiff = Int(mdiff / 60)                                                     ' Total hours elapsed
    mdiff = mdiff - hdiff * 60                                                ' Minutes difference
    timediff = hdiff & "Hours, " & mdiff & " Minutes"
   
End Function
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 400 total points
ID: 16286635
here is  function to get elapsed time
place this codes in a module

Function GetElapsedTime(interval)

 Dim totalhours As Long, totalminutes As Long, totalseconds As _
     Long
 Dim Days As Long, Hours As Long, Minutes As Long, Seconds As Long

 Days = Int(CSng(interval))
 totalhours = Int(CSng(interval * 24))
 totalminutes = Int(CSng(interval * 1440))
 totalseconds = Int(CSng(interval * 86400))
 Hours = totalhours Mod 24
 Minutes = totalminutes Mod 60
 Seconds = totalseconds Mod 60

 ' GetElapsedTime = days & " Days " & hours & " Hours " & Minutes & _
       " Minutes " & Seconds & " Seconds "

 'for hour and minutes just use
 
 '  GetElapsedTime= Hours  & " Hours " & Minutes & "Minutes"
 '  or
   
   GetElapsedTime= Hours &":" & Minutes
 End Function

to use the function

 set the controlSource of the  textbox to
     ControlSource: =GetElapsedTime([EndDate]-[StartDate])
0
 
LVL 61

Expert Comment

by:mbizup
ID: 16286645
Also check this function for ful Years, days, hours, minutes:

http://www.techonthenet.com/access/questions/elapsed.php
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Accepted Solution

by:
GRayL earned 1200 total points
ID: 16290314
Give this a try:

=Int(datediff("n",d1,d2)/60) & ":" & datediff("n",d1,d2) mod 60

d1 is the earlier datetime.  It gives you an answer like:  12:34
0
 
LVL 1

Author Comment

by:Lou Dufresne
ID: 16307537
Thank you all for your assistance. The code that best fits my circumstances for this project was submitted by GRayL.

Thank you all for your assistance.

Lou
0
 
LVL 61

Expert Comment

by:mbizup
ID: 16309511
Glad to help!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 16311160
Thanks, glad it helped.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…
Suggested Courses

834 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