compare dates between records

[Gostap via AccessMonster.com]

Hi there

A question from a newbie, hope some one could help me.

I have a table with some date fields in among others.
One field is called “Start production†and another is called “Stop
productionâ€
I want to do a comparison between “Stop production†and “Start production†at
the NEXT record! It is meant to get an msgBox with a warning if time between
“Stop production†and “Start production†at next record exceed e.g. 5 minutes

recID Start production Stop production

1011 2007-10-01 15:33 2007-10-01 16:11
1012 2007-10-01 16:12 2007-10-01 18:32
1013 2007-10-01 18:41 2007-10-01 19:13
1014 2007-10-01 19:14 2007-10-01 21:36

I hope it is not too messy!

Best Regards

Gostap

 

[Allen Browne]

So when you enter a new record, you want to compare the [Start production]
time with the nearest preceeding [Stop production] time?

Use the BeforeUpdate event procedure of the form to look up the maximum stop
production time before the start production. If this is less than 5 * 60
seconds, provide the warning.

This kind of thing (untested aircode):

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere as String
Dim varResult As Variant
Dim strMsg As String

If Not IsNull(Me.[Start production]) Then
strWhere = "[End production] < " & _
Format(Me.[Start production], "\#mm\/dd\/yyyy hh\:nn\:ss\#")
varResult = DMax("[Stop production]", "Table1", strWhere)
If DateDiff("s", varResult, Me.[Start production]) < 300 Then
strMsg = "Ended at " & Format(varResult, "General Date") &
vbCrLf & _
"Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Caution") <> vbYes
Then
Cancel = True
End If
End If
End If
End Sub

 

[Gostap via AccessMonster.com]

It works!
Thank you very much!

BG

Gostap

So when you enter a new record, you want to compare the [Start production]
time with the nearest preceeding [Stop production] time?

Use the BeforeUpdate event procedure of the form to look up the maximum stop
production time before the start production. If this is less than 5 * 60
seconds, provide the warning.

This kind of thing (untested aircode):

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere as String
Dim varResult As Variant
Dim strMsg As String

If Not IsNull(Me.[Start production]) Then
strWhere = "[End production] < " & _
Format(Me.[Start production], "\#mm\/dd\/yyyy hh\:nn\:ss\#")
varResult = DMax("[Stop production]", "Table1", strWhere)
If DateDiff("s", varResult, Me.[Start production]) < 300 Then
strMsg = "Ended at " & Format(varResult, "General Date") &
vbCrLf & _
"Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Caution") <> vbYes
Then
Cancel = True
End If
End If
End If
End Sub

Hi there

[quoted text clipped - 22 lines]

Gostap