Calculate Date/Time with Access!

[hasnsas]

I have a problem that's almost driving me crazy. There is this program am trying to develop that is complicated. We run a company that receives calls from clients about breakdowns, we record the time and date the call came in, the time when our technician reported to the site to rectify the breakdown and the time when the technician finished repairing the breakdown. What i want is a program that calculates the date and time difference between two dates. Our working hours are 8:00am to 5:30PM, meaning any call that comes after 5pm and before 8am should be automatically sent to the next day by the program. This program should also be in position to eliminate weekends that is Saturday and Sunday so any call that is reported on weekends should be pushed to Monday 8am. I have a code that i developed but it doesnot have the 8am to 5:30pm calculation and it does not eliminate weekends please if anyone could help me debug it and include this, i would be saved from exploding. Thanks. Below is the code in my module;

Option Compare Database
Option Explicit

'constants that one can use in time calculations
Public Const c_dblHour   As Double = 4.16666666666667E-02 ' = (1/24)
Public Const c_dblMinute As Double = 6.94444444444444E-04 ' = ((1/24/60)
Public Const c_dblSecond As Double = 1.15740740740741E-05 ' = (((1/24)/60)/60)

'-----------------------------------------------------------------------------------------
' Procedure : fncReturnHours
' Created   : Jan 11 2005 07:43
' Reference : fncReturnHours*
' Author    : Michael Reese
' Input(s)  : request date and time, response date and time, business open, business close
' Output(s) : hours between request and response, business hours only
' Purpose   : figure out business hours between request and response time
' Qualifier : 1. The request and response dates are both workdays
' Qualifier : 2. There are no holidays to factor in
' Qualifier : 3. Request and response date/times occur during business hours
'-----------------------------------------------------------------------------------------
Public Function fncReturnHours(ByVal datRequest As Date, _
                               ByVal datResponse As Date, _
                               ByVal datDayStart As Date, _
                               ByVal datDayEnd As Date) _
                               As Double

'|<------ 90-character width -------------------------------- 90-character width ------->|

PROC_DECLARATIONS:
   Dim dblBusinessHours As Double  'Fraction of day for business hours
   Dim dblStartHours    As Double  'Time difference between request time and end of business day
   Dim dblStopHours     As Double  'Time difference between response time and start of business day
   Dim dblReturnHours   As Double  'Total time elapsed; assing to function output
   Dim lngDayCounter    As Long
   
PROC_START:
   On Error GoTo PROC_ERROR
   dblReturnHours = 0
   
PROC_MAIN:
   'determine amount of time for business hours
   dblBusinessHours = datDayEnd - datDayStart
      
   'calculate amount of time from request time to end of first day
   dblStartHours = (Int(datResponse) + datDayEnd) - datResponse
   
   'calculate amount of time from start of last day to response time
   dblStopHours = datRequest - (Int(datRequest)) - datDayStart
   
   'determine if request and response occured on same day
   If Int(datRequest) = Int(datResponse) Then
      'same day, calculate time period only
      dblReturnHours = datResponse - datRequest
   Else
      'use loop; int function creates integer values out of dates
      For lngDayCounter = CLng(Int(datRequest)) To CLng(Int(datResponse))
         If lngDayCounter = Int(datRequest) Then
            dblReturnHours = dblReturnHours + dblStartHours
         ElseIf lngDayCounter = Int(datResponse) Then
            dblReturnHours = dblReturnHours + dblStopHours
         Else
            If Weekday(lngDayCounter, vbSaturday) > 2 Then
               dblReturnHours = dblReturnHours + dblBusinessHours
            End If
         End If
      Next
   End If
   
   'convert days to hours
   dblReturnHours = (dblReturnHours * 24)
   
PROC_EXIT:
   fncReturnHours = dblReturnHours
   Exit Function

PROC_ERROR:
   MsgBox "Error " & Err.Number & " (" & _
           Err.Description & ")" & vbCrLf & vbCrLf & _
           "Procedure: fncReturnHours" & vbCrLf & _
           "Module: Module1"
   GoTo PROC_EXIT

End Function

Below is the query i have:

SELECT tblTimes.RequestTime, tblTimes.ResponseTime, fncReturnHours([tblTimes.RequestTime],[tblTimes.ResponseTime],#12/30/1899 8:0:0#,#12/30/1899 17:30:0#) AS TotalHours, tblTimes.Site, tblTimes.RefNo, tblTimes.FinishTime, tblTimes.NF, tblTimes.NA, tblTimes.DO, tblTimes.DC, tblTimes.RDT
FROM tblTimes;

 

[DCrake]

What might your job easier is to validate the date and time the call was received before the record is updated in the table. Don't know how you are collecting this info but if the user enters a time that is after closing time then get your app to change the time to a revised date/time. Likewise if the day is a weekend then get it to change it to a Monday.

What you can have is Actual date/times and revised dates/times and use the latter to calculate the elapsed times.

For example if an actual time is recorded as being 5:45 pm on a Monday the revised date/time would be 08:00 am Tuesday.

Writing simple little functions to convert actual to revised would also work.

 

[gemma-the-husky]

gawd - its took ages to post this
----------------------------------


its easier to use internal date time format

internal date/time stores the time down to seconds

so

mydatetimevariable = date() - stores the date value only
mydatetimevariable = time() - stores the time value only
mydatetimevariable = now() - stores the date and time value only

so given two such times you can say

timediff - mydatevariable1 - nydatevariable2

NOW - this time is resolved in terms of days, and day fractions

so a value like 2.43126785 say

means 2 days, and .43126875 of a day

so multiple the remainder by 24, and you get hours
10.35045 hours ie 10 hours and a fraction

so multiple the fraction by 60, and you get minutes
.35045 hours = 21.027 minutes

and multiple the remainder by 60 and you get seconds
.027 = 1.62 seconds

----------
far easier than trying to deconstruct a date/time

=======================
ALTERNATIVE

if you are timing a process, try the TIMER function - it records number of elapsed seconds

dim times(2) as long

'start process
times(1) = timer

''''
''''
do process
''''
''''
times(2) = timer

msgbox("time taken = " & times(2) - times(1) & " seconds")