Calculate DateDiff when criteria is met

1. ISM Novice Windows 7 64bit Access 2010 32bit  Join Date
Mar 2015
Posts
4

Calculate DateDiff when criteria is met

I have a very simple table. I would like to calculate the time that has elapsed if a condition is met.

Table captures this

Name
Date
Time
Event (field can have either N, BM, S)

The event field will be captured 3 x daily, 8:15am, 2:15pm, 10:15pm

what I am trying to do is calculate the time difference from the first time a N appeared and 9 consecutive N's are entered. So If:

2/25/17 had a N for all 3 shifts
2/26/17 had a N for all 3 shifts
2/28/17 had a N for all 3 shifts

9 consecutive N's would be entered and 72 hours would have elapsed, I would then make a cmd button visible.

The calculation would always have to be from the time the first N is entered. So if entries were N,N,N,N, S, BM, S, ...It would reset the next time a N is entered.

Make sense?  Reply With Quote

2. Competent At Times Windows 10 Access 2013 64bit       Join Date
Nov 2011
Location
Posts
3,936
This routine will return a count of hours for one Name, see the second routine below which will test the function and pass a name parameter. I added a field to your table which was the number of hours. This does not need to be kept on the table, it is rather repetitious, it can be constants in the function - if shift is 1 then hours is 8, etc.

Code:
Private Function CalcNs(NameF)
Dim strSQL As String, rst As Recordset, CountHours As Integer

On Error Resume Next
strSQL = "SELECT Table8.NameF, Table8.DateF, Table8.TimeShift, Table8.Event, Table8.HoursF FROM Table8 WHERE NameF='" & NameF & "' ORDER BY Table8.NameF, Table8.DateF DESC , Table8.TimeShift DESC;"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
If Err > 0 Then
MsgBox "No records found"
CalcNs = 0
Exit Function
End If

Do Until rst.EOF
If rst!Event = "N" Then
CountHours = CountHours + rst!HoursF
Else
Exit Do
End If
rst.MoveNext
Loop
CalcNs = CountHours
End Function

Public Function TestCalcNs()
Dim HoursCnt As Integer
HoursCnt = CalcNs("a")
Debug.Print HoursCnt
End Function  Reply With Quote

3. VIP Windows 7 64bit Access 2007     Join Date
Nov 2015
Posts
1,378
Maybe another way to do it that I think would work:

1. Add numeric field to table and form(hide it on form) called NCnt. In he AfterUpdate of the Event field, put this:

If Me.Event = "N" Then
If NCnt = 0 Then
NCnt = 1
Else
NCnt = NCnt + 1
If NCnt > 8 then
Me.cmdbutton.Visible = True
Else
Me.cmdbutton.Visible = False
End If
End If
Else

Me.NCnt = 0
End If

If you want that button visible when they get to that form if N has reached 9 already you could put this part in the OnCurrent Event of form:

If NCnt > 8 then
Me.cmdbutton.Visible = True
Else

Me.cmdbutton.Visible = False
End If  Reply With Quote

4. VIP Windows 7 64bit Access 2007     Join Date
Nov 2015
Posts
1,378
Actually for my code above, you will need a new table tblNCnt with one field NCnt(numeric).

So code in AfterUpdae would be(if syntax is correct):

Me.SetWarnnigs False
vNCnt = DLookup("[NCnt]", "tblNCnt)
If Me.Event = "N" Then
If vNCnt = 0 Then
DoCmd.RunSQL "Update tblNCnt Set NCnt = 1"
Else
vNCnt = vNCnt + 1
DoCmd.RunSQL "Update tblNCnt Set NCnt = " & vNCnt
If vNCnt > 8 then
Me.cmdbutton.Visible = True
Else
Me.cmdbutton.Visible = False
End If
End If
DoCmd.RunSQL "Update tblNCnt Set NCnt = 1"
End If
Me.SetWarnnigs True  Reply With Quote Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Other Forums: Microsoft Office Forums