working out time difference

[moonman84]

Hi there,

I am gathering data from a alarm monitoring system. It is writing to a Access database. At this moment it is writing to 2 tables, 1 being "faults" the other being "normal". sample of the tables are as follows:

Table1(faults)
Name Fault Time
gas1 1 27/06/2011 15:40:35
gas1 1 27/06/2011 15:42:35

Table2(normal)
Name Fault Time
gas1 0 27/06/2011 15:41:35
gas1 0 27/06/2011 15:43:35

I am trying to show in a report the duration of the Faults. So from time 15:40:35 to 15:41:35. I am not using any ID fields on the tables, the names are the same on both tables but table1 will always have 1 in the fault column and table2 will have 0 in the fault column times will be as and when data is entered.

I would like some advice weather I should use a query or use VBA and how to go about it...

Many thanks for your time and help.

[apr pillai]

I have written some VBA Code with some sample data created in Excel and uploaded in Access. The sample code is given below:

Public Function TimeDiff()
Dim db As Database, rst As Recordset
Dim xstart As Double, xend As Double, diff As Double

Set db = CurrentDb
Set rst = db.OpenRecordset("Sheet1", dbOpenTable)
rst.Index = "idx"
xstart = rst![faulttime]
xend = xstart
rst.MoveNext
Do While Not rst.EOF
     If (rst![faulttime] - xstart) > TimeValue("1:00:00") Then
         xstart = rst![faulttime]
     Else
        xend = rst![faulttime]
        diff = xend - xstart
        rst.Edit
        rst![diff] = diff
        rst.Update
        xstart = xend
     End If
     rst.MoveNext
Loop
rst.Close

The image of the sample data after running the above code with the result in a new column (diff) is attached for info. An index is created on the FaultTime field to order the data correctly. When the time difference between two dates is more than 1 hour the record is skipped forward assuming that the actual faulttime taken place some other time.

Since, only few records are tested there may be logical errors when large amount of data are involved. Correct the logic wherever you find it is failing by running the code on original file and manually checking the result.