Wrong Elapsed time calculations

[jethro11]

Hi,
I've defined a custom function called Downtime as coded below which I then use to calculate elapsed time between 2 sets of dates and times. This works fine except when the dates span midnight and then I get really big numbers for downtime. For eg if Stop Date is 12/15/2002 and Stop Time is 23:00 and the Start Date is 12/16/2002 and Start Time is 01:00, instead of 120 minutes I get 525720. Here Stop date and Time refer to the time when the machine stops and Start date. I would appreciate any help with this as I've struggled with this problem for real long.

Public Function Downtime(ByVal Start_Time As Date, ByVal Stop_Time As Date, ByVal Start_Date As Date, ByVal Stop_Date As Date) As Double

Dim StartDate As String
Dim EndDate As String
Dim dStartDate As Date
Dim dEndDate As Date

StartDate = Format(Stop_Date, "mmm/dd/yyy&quot & " " & Format(Stop_Time, "Short Time&quot
dStartDate = CDate(StartDate)
EndDate = Format(Start_Date, "mmm/dd/yyy&quot & " " & Format(Start_Time, "Short Time&quot
dEndDate = CDate(EndDate)


Downtime = DateDiff("n", dStartDate, dEndDate)

End Function

 

[JeremyNYC]

Jethro,

Store the time and date in one field. That's how the time/date format works in Access. It stores both parts,whether you supply both parts or only one. Just put them both in one field and you'll find that your calculations are much easier to do. If you ever need to parse the value into two values, just use the format function.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[KenReay]

Hi

Just an absolutely wild guess, but how about

StartDate = Format(Stop_Date, "mmm/dd/yyyy&quot & " " & Format(Stop_Time, "Short Time&quot
dStartDate = CDate(StartDate)
EndDate = Format(Start_Date, "mmm/dd/yyyy&quot & " " & Format(Start_Time, "Short Time&quot
dEndDate = CDate(EndDate)


so there can be no doubt about the year (ie yyyy not yyy)
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jethro11]

Ken and Jeremy,
Thanks for your suggestions, I tried to format it as yyyy and it worked, so that was the source of the confusion! The form was designed by somebody else with different fields for date and time and the database is already populated with a years worth of data so changing the form design is tough.
I'll keep in mind the need to have a single field for date/time for future reference. Thanks again guys!