Report and days since

[jwillet1]

I have a database that stores information for lab testing. Each time a tech does a "step" in the test process he logs it in the table, using an input form. There are different categories, for example preparation, testing, analysis, etc, and each of those steps take time. I have the form autopopulate the date and time with NOW() evertime the form is updated. What i want to do is calculate the time it takes to do each in days. I can easily get how many days it was from now since they logged the test, =NOW()-TestDateTime. What I want to do is get the number of days it took to do each step, ie the number of days between each event. Is there a way to do this?

I just want to say that I am new to Access and your forum has really been an excellent help in teaching an Access newbie.

 

[apr pillai]

If each event is recorded on separate rows then take a look at the following link for an example with code:

Finding Difference between dates in Rows of a Column

 

[jwillet1]

Ok so I went to the link provided and imported and used the code and it works mostly as I want it to.

In my case I have a list of test items with categories, i would like the math calculation for the days to only fire if the test items are the same.

Public Function FrequencyCalc()
'----------------------------------------------------------
'Author: a.p.r.pillai
'Date  : March 2013
'All Rights Reserved by msaccesstips.com
'----------------------------------------------------------
Dim db As Database, rst1 As Recordset, rst2 As Recordset
Dim m_diff As Double
On Error GoTo FrequencyCalc_Error
Set db = CurrentDb
'Open tblOrdersQ's first instance and position on the first record
Set rst1 = db.OpenRecordset("qryTestDataDaysPerPhaseStatus", dbOpenDynaset)
'Open tblOrdersQ's second instance and position on the second record
Set rst2 = db.OpenRecordset("qryTestDataDaysPerPhaseStatus", dbOpenDynaset)
rst2.MoveNext
'Find difference between dates from first & second instances of OrderDates
'in the same Query.
'update number of days in the second record onwards.
Do While Not rst1.EOF
   m_diff = rst2!testDateTime - rst1!testDateTime
   If Not rst2.EOF Then
     With rst2
        .Edit
        !days = m_diff
        .Update
      rst1.MoveNext
        .MoveNext
      End With
      If rst2.EOF Then
         Exit Do
      End If
   End If
Loop
rst1.Close
Set rst1 = Nothing
rst2.Close
Set rst2 = Nothing
db.Close
Set db = Nothing
FrequencyCalc_Exit:
Exit Function
FrequencyCalc_Error:
MsgBox Err & " : " & Err.Description, , "FrequencyCalc()"
Resume FrequencyCalc_Exit
End Function

The test item is stored in a field called, "productTestID" this is a unique value and i only want the days math to happen if the "current" and "next" testDateTime are for the same vehicle.

I am thinking something like this

If rst1!productTestID = rst2!productTestID

 

[jwillet1]

I think i figured out how to do it on my own. If there is a better way let me know.

The next thing i want to do is if we are moving to a new test product, it is giving me a zero, I'd like no data if it is a new test product.

Here is the do loop i am currently using:

Do While Not rst1.EOF
   m_diff = rst2!testDateTime - rst1!testDateTime
   If rst1!vehicleNumber = rst2!vehicleNumber Then
      If Not rst2.EOF Then
        With rst2
           .Edit
           !days = m_diff
           .Update
         rst1.MoveNext
           .MoveNext
         End With
         If rst2.EOF Then
            Exit Do
         End If
      End If
   Else: rst1.MoveNext
   rst2.MoveNext
   End If
Loop