Calculating time against certain criteria

C

Colin DIxon

Hello,

I want to check if a service call has fallen within a given time period when
a record is closed and the finish time is entered.
If I want to set a level 1 as 4 hrs, level 2 as 8 hrs etc., how do I create
a formula in a time field to see if the time the call has finished has
elapsed or not, E.g. -

Level 1 = 4hrs
Time job created 12:00
Job to complete time 16:00
Time job closed 17:00
Out of Response by 1 hr

I assume I need a formula in a field expression that determines the level
and compares the time the job was created to the time the job was closed
dependant on the level criteria.

In essence I want to check if the job met a timeframe automatically when the
job is closed against different Levels. I would need to now the formula to
read the level, start time and finish and the result.

I hope this makes sense.

A big thank you to all you experts out there - you are great.

Colin
 
A

Andy - UK Access User Group

You will be lucky to get all the logic in a single expression, but if
you write some VBA code of the form on say the beforeupdate event then
you can write a function to do this

Assume the form has controls StartDate, ClosedDate, Level and Missed
(boolean).

Then something like the following should work :-

Dim TargetDate As Date
Select Case Level
Case 1: TargetDate = DateAdd("h", 4, StartDate)
Case 2: TargetDate = DateAdd("h", 8, StartDate)
End Select

If DateDiff("h", ClosedDate, TargetDate) < 0 Then
Missed = True
Else
Missed = False
End If

Alternatively you could write an expression using a combination of IIF,
DateDiff and DateAdd, but it would be quite messy.
 
C

Colin DIxon

HI Andy,

I have entered the code as follows in the 'BeforeUpdate' part of the OOR
Time as an event procedure - is this correct only I cannot get it to work;
I am actually using the Time rather than date, could this be the problem?

Private Sub OOR_Time_BeforeUpdate(Cancel As Integer)

Dim TargetDate As Date
Select Case Priority
Case1: TargetDate = DateAdd("h", 4, CallOpened)
Case2: TargetDate = DateAdd("h", 8, CallOpened)
Case3: TargetDate = DateAdd("h", 16, CallOpened)
Case4: TargetDate = DateAdd("h", 24, CallOpened)
End Select

If DateDiff("h", CallClosed, TargetDate) < 0 Then
oortime = True
Else
oortime = False
End If

End Sub

I have the following controls on the form, Call Opened, Call Closed,
Priority and OOR Time.

Thanks for your help.
 
Top