AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
Question QUESTION: I need to know how to do a date calculation where it puts the days between two dates in one box the left over hours in another and finally any minutes left over in the last box. I have tried Datediff but it seems to mess up and return a negative value when the close time is before the open time field even though the close date is after the open date field. To be more clear if my open date was 01/02/2009 12:15 AM and my close field was 01/03/2010 1:30 AM I would want the boxes to populate with 366 days 1 hour 15 minutes. The two fields are in the General date format with a date and time attached.
My first box has the following expression in it
=DateDiff("d",[LCODT],[CloseDT])
Second Hour Box
=Abs(DateDiff("h",[LCODT],[CloseDT])-([DayCal]*24))
Third Minute Box(box giving me trouble)
=Abs(DateDiff("n",[LCODT],[CloseDT])-([DayCal]*24*60))-([HourCal]*60))
Any help is greatly appreciated
ANSWER: I would do it differently:
First calc:
=DateDiff("d",[LCODT],[CloseDT])
Second calc:
=DateDiff("h",[LCODT],[CloseDT]) MOD DateDiff("d",[LCODT],[CloseDT])
Third Calc:
=DateDiff("n",[LCODT],[CloseDT]) MOD DateDiff("h",[LCODT],[CloseDT])
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: These expressions is not working, I am not sure whats going on but for example my open date/time is
6/17/2009 8:45:00 AM
and my close date/time is
6/17/2010 5:00:00 AM
and the code from above yields
365 days 362 hours 8712 minutes
instead of
364 days 20 hours 15 minutes
or something similar is what I am after
Thanks for the quick response
ANSWER: Hmm they worked with your first example. This is easier to do in a function:
Public Function ElapsedTime(dteStart As Date, dteEnd As Date) As String
Dim lngDays As Long
Dim intMinutes As Integer, intHours As Integer
ElapsedTime = lngDays & " Days " & intHours & " Hours and " & intMinutes & " Minutes"
End Function
This function returns the string:
364 Days 20 Hours and 15 Minutes
for the dates you gave.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: ok, I am still getting an error. How would you place this in your report? I tried putting the code in and then pasting the elapsed time in the text box as =[ElapsedTime], but I am guessing this in incorrect. Thanks for your help
Answer Yep, the best way would be to do it in your query. Add a column to your query like so:
Time Past: ElapsedTime([LCODT],[CloseDT])
This will calculate the elapsed time for each record. Then you can just assign a control to the Time Past column.
As background Elapsed time is a function, you would use it like any function. So you could put it into a control like this:
=ElapsedTime([LCODT],[CloseDT])
What you did was tell Access it was a filed or control name, but putting the brackets around it. Since no field or control existed you get an error. Like manyu functions you have to pass parameters to it. So typing =ElapsedTime() still wouldn't have worked.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
