Results 1 to 4 of 4
  1. #1
    ISM is offline 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?

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    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

  3. #3
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    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

  4. #4
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 11-04-2016, 11:53 PM
  2. Replies: 3
    Last Post: 02-16-2016, 05:02 PM
  3. Calculate DateDiff on Time Added Field
    By athyeh in forum Queries
    Replies: 9
    Last Post: 12-18-2013, 02:10 PM
  4. Replies: 1
    Last Post: 05-01-2013, 10:53 AM
  5. Replies: 7
    Last Post: 07-25-2011, 02:50 AM

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