code help, date diff not being read. . HELP

[EBee]

The code below matches machine numbers from two diff tables, after it finds a match I want it to compare if the time difference, if less than 60 seconds and if the Transaction = 7 or 8 or 9 or 10. If all the criteria is met then move the records to another table. For some reason it is matching the machine number but not matching the time. Please help




Private Sub Command5_DblClick(Cancel As Integer)




On Error GoTo error_Handling
Dim db As Database
Dim rst As Recordset
Dim rstOpen As Recordset
Dim rstFirst As Recordset
Dim rstExcept As Recordset
Dim rstSecond As Recordset
Dim intAnswer As Integer
Dim trap_error As Integer


intAnswer = MsgBox(" Did you Run the 2 Bart Report ?", vbYesNo)
If intAnswer = vbNo Then
MsgBox " Process Terminated. . GO TO Bart and Run Auxil2 and AuxilOpen , Thank You"
DoCmd.CancelEvent
Else




Set db = CurrentDb
Set rst = db.OpenRecordset("auxil1", dbOpenDynaset)
Set rstOpen = db.OpenRecordset("Auxil_Logic_Open&quot
Set rstFirst = db.OpenRecordset("table6&quot
Set rstExcept = db.OpenRecordset("tableExcept&quot
Set rstSecond = db.OpenRecordset("table789&quot

error_Handling:

If Err = 3021 Then
trap_error = MsgBox(" The tables maybe empty!!, OR No Match found ", vbOKOnly, "Custom Error Handler&quot
If trap_error = vbOK Then
Exit Sub
Else
End If
End If

rstOpen.MoveFirst
rst.MoveFirst
Do While Not rst.EOF


rst.FindFirst "Number=" rstOpen!Number
If Not rstOpen.NoMatch
If rst!Trans_Number = 7 Or 8 Or 9 Or 10 And (DateDiff(&quot;s&quot;, rst!Tx_Time, rstOpen!Tx_Time) < 10 Then

With rstSecond
.AddNew
!Number = rstOpen!Number
!Denom = rstOpen!Denom
!Location = rstOpen!Location
!Emp_Name = rstOpen!Emp_Name
!Tx_Date = rstOpen!Tx_Date
!Tx_Time = rstOpen!Tx_Time
!Trans_Number = rstOpen!Trans_Number
!Trans_Desc = rstOpen!Trans_Desc
.Update
End With

With rstSecond
.AddNew
!Number = rst!Number
!Denom = rst!Denom
!Location = rst!Location
!Emp_Name = rst!Emp_Name
!Tx_Date = rst!Tx_Date
!Tx_Time = rst!Tx_Time
!Trans_Number = rst!Trans_Number
!Trans_Desc = rst!Trans_Desc
.Update
End With
rst.Delete
rstOpen.Delete



End If

rstOpen.MoveNext
Loop



MsgBox &quot;PROCESS DONE , Thank you &quot;



End If

 

[papparuff]

Change the following code:

If rst!Trans_Number = 7 Or 8 Or 9 Or 10 And (DateDiff(&quot;s&quot;, rst!Tx_Time, rstOpen!Tx_Time) < 10 Then

to

Select Case rst!Trans_Number
Case 7 to 10
if DateDiff(&quot;s&quot;, rst!Tx_Time, rstOpen!Tx_Time) < 10 Then
' the rest of your code...
endif
End Select

rst.movenext
John Ruff - The Eternal Optimist

 

[EBee]

I am still getting errors, what am I doing wrong

thanks

Do While Not rst.EOF

'rst.MoveFirst
rst.FindFirst &quot;Number=&quot; & rstOpen!Number ' compares logic open door with electronic sign
If Not rstOpen.NoMatch Then
'rst.MoveNext

' If rst!Trans_Number = 7 Or rst!Trans_Number = 8 Or rst!Trans_Number = 9 Or rst!Trans_Number = 10 And (DateDiff(&quot;s&quot;, rst!Tx_Time, rstOpen!Tx_Time) < 60) Then
Select Case rst!Trans_Number
Case 7 To 10
If DateDiff(&quot;s&quot;, rst!Tx_Time, rstOpen!Tx_Time) < 10 Then

With rstSecond
.AddNew
!Number = rstOpen!Number
!Denom = rstOpen!Denom
!Location = rstOpen!Location
!Emp_Name = rstOpen!Emp_Name
!Tx_Date = rstOpen!Tx_Date
!Tx_Time = rstOpen!Tx_Time
!Trans_Number = rstOpen!Trans_Number
!Trans_Desc = rstOpen!Trans_Desc
.Update
End With

With rstSecond
.AddNew
!Number = rst!Number
!Denom = rst!Denom
!Location = rst!Location
!Emp_Name = rst!Emp_Name
!Tx_Date = rst!Tx_Date
!Tx_Time = rst!Tx_Time
!Trans_Number = rst!Trans_Number
!Trans_Desc = rst!Trans_Desc
.Update
End With
rst.Delete
rstOpen.Delete

End If

End Select
rst.MoveNext

Loop
'rst.MoveNext

End If

MsgBox &quot;PROCESS DONE , Thank you &quot;



End Sub

 

[papparuff]

What kind of errors are you getting? Have you stepped through the code to see if your criteria is being met? John Ruff - The Eternal Optimist

 

[EBee]

I am not getting any error now, but it still not reading the time stamp.

Private Sub Command5_DblClick(Cancel As Integer)


On Error GoTo error_Handling
Dim db As Database
Dim rst As Recordset
Dim rstOpen As Recordset
Dim rstFirst As Recordset
Dim rstExcept As Recordset
Dim rstSecond As Recordset
Dim intAnswer As Integer
Dim trap_error As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset(&quot;auxil1&quot;, dbOpenDynaset)
Set rstOpen = db.OpenRecordset(&quot;Auxil_Logic_Open&quot
Set rstFirst = db.OpenRecordset(&quot;table6&quot
Set rstExcept = db.OpenRecordset(&quot;tableExcept&quot
Set rstSecond = db.OpenRecordset(&quot;table789&quot

intAnswer = MsgBox(&quot; Did you Run the 2 Bart Report ?&quot;, vbYesNo)
If intAnswer = vbNo Then
MsgBox &quot; Process Terminated. . GO TO Bart and Run Auxil2 and AuxilOpen , Thank You&quot;
DoCmd.CancelEvent
Else


error_Handling:

If Err = 3021 Then
trap_error = MsgBox(&quot; The tables maybe empty!!, OR No Match found &quot;, vbOKOnly, &quot;Custom Error Handler&quot
If trap_error = vbOK Then
Exit Sub
Else
End If
End If

rstOpen.MoveFirst
rst.MoveFirst
Do While Not rst.EOF

rst.MoveNext
rstOpen.MoveNext

rst.FindFirst &quot;Number=&quot; & rstOpen!Number
If Not rstOpen.NoMatch Then

Select Case rst!Trans_Number
Case 7 To 10
If DateDiff(&quot;s&quot;, rst!Tx_Time, rstOpen!Tx_Time) < 60 Then

With rstSecond
.AddNew
!Number = rstOpen!Number
!Denom = rstOpen!Denom
!Location = rstOpen!Location
!Emp_Name = rstOpen!Emp_Name
!Tx_Date = rstOpen!Tx_Date
!Tx_Time = rstOpen!Tx_Time
!Trans_Number = rstOpen!Trans_Number
!Trans_Desc = rstOpen!Trans_Desc
.Update
End With

With rstSecond
.AddNew
!Number = rst!Number
!Denom = rst!Denom
!Location = rst!Location
!Emp_Name = rst!Emp_Name
!Tx_Date = rst!Tx_Date
!Tx_Time = rst!Tx_Time
!Trans_Number = rst!Trans_Number
!Trans_Desc = rst!Trans_Desc
.Update
End With
rst.Delete
rstOpen.Delete

End If

End Select
End If

Loop

End If

MsgBox &quot;PROCESS DONE , Thank you &quot;



End Sub

 

[papparuff]

When you step through your code, what values are

rst!Tx_Time and rstOpen!Tx_Time in the line:
DateDiff(&quot;s&quot;, rst!Tx_Time, rstOpen!Tx_Time) < 60

showing?

Actually add another variable to your code:

dim varSec as variant

Change
Case 7 To 10
If DateDiff(&quot;s&quot;, rst!Tx_Time, rstOpen!Tx_Time) < 60 Then

To

Case 7 To 10
varSec = DateDiff(&quot;s&quot;, rst!Tx_Time, rstOpen!Tx_Time)
debug.print varsec
stop
If varSec < 60 Then

Then open your debug window and see what value varSec is. John Ruff - The Eternal Optimist

 

[EBee]

rstOpen table data
Number Denom Location Fill_Trans Card_Number EMP_Name TX_Date TX_Time Trans_Number Trans_Desc
50895 1.00 CR 01-03-61 No 3C63000340 FOERSCHLER, D 3/15/02 9:44:48 AM 4 Door Open
50895 1.00 CR 01-03-61 No 3C63000340 FOERSCHLER, D 3/15/02 9:44:52 AM 4 Door Open
50895 1.00 CR 01-03-61 No 3C63000340 FOERSCHLER, D 3/15/02 9:45:16 AM 4 Door Open
50819 1.00 CR 01-03-61 No 3C33000310 FOERSCHLER, DAVE 3/15/02 9:54:22 AM 4 Door Open
50819 1.00 CR 01-03-61 No 0000000000 3/15/02 9:59:51 AM 4 Door Open
50819 1.00 CR 01-03-61 No 0000000000 3/15/02 9:59:53 AM 4 Door Open

rst table data

Number Denom Location Fill_Trans Card_Number EMP_Name TX_Date TX_Time Trans_Number Trans_Desc
31203 0.25 CR 01-02-80 1 0000000000 3/15/02 9:03:14 AM 7 Auxilary Fill
50404 1.00 CR 01-03-61 1 3CC50004A3 WALLACE, WALLY 3/15/02 4:31:04 AM 7 Auxilary Fill
50819 1.00 CR 01-03-61 1 3C33000310 FOERSCHLER, DAVE 3/15/02 9:55:06 AM 7 Auxilary Fill
50895 1.00 CR 01-03-61 1 3C63000340 FOERSCHLER, D 3/15/02 9:47:18 AM 7 Auxilary Fil


AFTER RUNNING THE CODE HERE IS WHAT I GET

Number Denom Location Fill_Trans Card_Number EMP_Name TX_Date TX_Time Trans_Number Trans_Desc
31203 0.25 CR 01-02-80 3/15/02 9:03:14 AM 7 Auxilary Fill
50404 1.00 CR 01-03-61 WALLACE, WALLY 3/15/02 4:31:04 AM 7 Auxilary Fill
50819 1.00 CR 01-03-61 3/15/02 9:59:51 AM 4 Door Open
50819 1.00 CR 01-03-61 FOERSCHLER, DAVE 3/15/02 9:55:06 AM 7 Auxilary Fill
50819 1.00 CR 01-03-61 FOERSCHLER, DAVE 3/15/02 9:54:22 AM 4 Door Open
50895 1.00 CR 01-03-61 FOERSCHLER, D 3/15/02 9:45:16 AM 4 Door Open
50895 1.00 CR 01-03-61 FOERSCHLER, D 3/15/02 9:47:18 AM 7 Auxilary Fill
50895 1.00 CR 01-03-61 FOERSCHLER, D 3/15/02 9:44:52 AM 4 Door Open

I should only get any match data that has the same machine number and trans 4 with trans 7 within 1 min

 

[papparuff]

Can you compact, zip up and send the program so I can check it out?

My address is papparuff@covad.net John Ruff - The Eternal Optimist

 

[EBee]

The file is big and this machine does not have a Zip file. . . however i pin pinted the problem

it is the DateDiff function

Date/Time data is

09:45:16 am
vs.
09:47:18 am
If DateDiff(&quot;nn&quot;, rst!Tx_Time, rstOpen!Tx_Time) < 1 Then

when it pass this code it does not recognize it with the diff of 1 min and keeps on going.. . it should have jump to the endif and capture the next record. How should i format the data so the it can get the real time difference

thanks

erwin

 

[EBee]

correction:

If DateDiff(&quot;n&quot;, rst!Tx_Time, rstOpen!Tx_Time) < 1 Then

If DateDiff(&quot;s&quot;, rst!Tx_Time, rstOpen!Tx_Time) < 60 Then

either of this needs a little tweek to work accurately

thanks a bunch
erwin