Storing a value with associated time

[rikesh]

Hello all,

I am a beginner so please go easy on me!



Situation
I have a database which contains a field 'status'. This status takes a numerical value (1, 2, 3, etc...). Everything works great.

What I want is to monitor the length of time a field spends on particular status. Specifically, I want to record the length of time that the record spends with a status value of 3. So if the record has its status changed to 3, the system 'starts the clock' and once the status changes away from 3, the system 'stops the clock'. This must also continue if the status subsequently returns to 3 at a later date (for the same record) and cumulatively produce the total amount of time that the record spent on status 3.

Practical example
March 19th: Record is set to status 3 (for the first time)
March 21st: Record is set to status 1
March 25th: Record is set to status 3 (again)
March 27th: Record is set to status 2

In this scenario, I want visibility that the record has spent four days on status 3 altogether. I don't need exact times (nearest day will do). Ideally the system will only count working days (although this is not a must-have).

Could anyone suggest a solution for what I am looking for? Many thanks in advance, I really appreciate your help.

[June7]

Need another table to store records of this data.

Run code in the BeforeUpdate event of Status combobox to save record ID, date/time, and status code.

If OldValue and NewValue are not the same then if OldValue or NewValue is 3 save StatusChange record.

Something like:

If Me.OldValue <> Me.NewValue Then
If Me.OldValue = 3 or Me.NewValue = 3 Then
CurrentDb.Execute "INSERT INTO StatusChange(RecID, ChangeDate, Status) VALUES(" & Me.ID, & ", #" & Now() & "#, " & Me.cmbStatus & ")"
End If
End If

[rikesh]

Need another table to store records of this data.

Run code in the BeforeUpdate event of Status combobox to save record ID, date/time, and status code.

If OldValue and NewValue are not the same then if OldValue or NewValue is 3 save StatusChange record.

Something like:

If Me.OldValue <> Me.NewValue Then
If Me.OldValue = 3 or Me.NewValue = 3 Then
CurrentDb.Execute "INSERT INTO StatusChange(RecID, ChangeDate, Status) VALUES(" & Me.ID, & ", #" & Now() & "#, " & Me.cmbStatus & ")"
End If
End If

Thank you for your reply. This looks like exactly what I am looking for.

I will be using this sometime next week - if any problems I'll post back haha, but thanks for your help I appreciate it.