[RESOLVED] how to calculate between times (login & logout)

[salsa31]

hey
i have a form with login in and log out options
now i added another field in the Database with the name Logtotal = (time in time out)
only when i log out and then log in i can see the log out time.
how do i calculate between those two field ?
after i log in ofcorse

this is the code that loads from the DB

Set RS = CN.Execute("SELECT * FROM Logs ORDER BY LogId")
    While Not RS.EOF
        Set Itm = FrmSecurityreport.LsVw.ListItems.Add(, , RS!LogId, , "report")
        Itm.Tag = RS!LogId
        Itm.bold = True
        Itm.SubItems(1) = RS!LogDate
        Itm.SubItems(2) = RS!LogDay
        Itm.SubItems(3) = RS!LogName
        Itm.SubItems(4) = RS!LogIn
      
   If Not IsNull(RS!logout) Then
Itm.SubItems(5) = RS!logout
End If
  If Not IsNull(RS!LogTotal) Then
Itm.SubItems(6) = RS!LogTotal
End If
    
         
        
      
        RS.MoveNext
        DoEvents
    Wend

tnx for the help

[dee-u]

Have a look for DATEDIFF.

[salsa31]

i try to put this in the form load or in the form activated got a error
object variable or type mismatch

 Dim daDate1 As Date
Dim daDate2 As Date
Dim intMins As Integer
Dim intHrs As Integer
daDate1 = CDate(Itm.SubItems(4))
daDate2 = CDate(Itm.SubItems(5))
intMins = DateDiff("n", daDate1, daDate2)
intHrs = intMins \ 60
intMins = intMins Mod 60
Itm.SubItems(6) = CStr(intHrs) & ":" & CStr(intMins)

[dee-u]

On what line does the error occurs?

[salsa31]

daDate1 = CDate(Itm.SubItems(4))

[dee-u]

It just means you haven't yet populated your listview upon trying to use it. You should first populate the listview or perform the calculation after populating the listview.

[salsa31]

i did populate

Private Sub Loadiaarry()
  Set RS = CN.Execute("SELECT * FROM Logs ORDER BY LogId")
    While Not RS.EOF
        Set Itm = FrmSecurityreport.LsVw.ListItems.Add(, , RS!LogId, , "report")
        Itm.Tag = RS!LogId
        Itm.bold = True
        Itm.SubItems(1) = RS!LogDate
        Itm.SubItems(2) = RS!LogDay
        Itm.SubItems(3) = RS!LogName
        Itm.SubItems(4) = RS!LogIn
      
   If Not IsNull(RS!logout) Then
Itm.SubItems(5) = RS!logout
End If
  If Not IsNull(RS!LogTotal) Then
Itm.SubItems(6) = RS!LogTotal
End If
    
         
        
       Bar.Value = Bar.Value + 1
        RS.MoveNext
        DoEvents
    Wend
    End Sub

[dee-u]

But you should first populate before doing that calculate where it involves the values coming from the listview.

[salsa31]

what do you meen?
give me en example

[salsa31]

Private Sub LoadList()
  Set RS = CN.Execute("SELECT * FROM Logs ORDER BY LogId")
    While Not RS.EOF
        Set Itm = FrmSecurityreport.LsVw.ListItems.Add(, , RS!LogId, , "report")
        Itm.Tag = RS!LogId
        Itm.bold = True
        Itm.SubItems(1) = RS!LogDate
        Itm.SubItems(2) = RS!LogDay
        Itm.SubItems(3) = RS!LogName
        Itm.SubItems(4) = RS!LogIn
      
   If Not IsNull(RS!logout) Then
Itm.SubItems(5) = RS!logout
End If
  If Not IsNull(RS!LogTotal) Then
Itm.SubItems(6) = RS!LogTotal
End If
    
         
        
       
        RS.MoveNext
        DoEvents
    Wend
    End Sub

still dosnt work

[Doogle]

I don't think I really understand the problem, but if you want to calculate the difference between the two items it's a simple matter of checking if a Logout time exists, if it does, check if the LogTotal has already been calculated, if it hasn't then calculate it.

If Not IsNull(rs![logout]) Then
'
' If there's a Logout time then
'   check if the LogTotal has been calculated
'
    Itm.subitems(5) = rs![logout]
    If IsNull(rs![LogTotal]) Then
        '
        ' Calculate LogTotal
        '
        daDate1 = CDate(Itm.subitems(4))
        daDate2 = CDate(Itm.subitems(5))
        intMins = DateDiff("n", daDate1, daDate2)
        intHrs = intMins \ 60
        intMins = intMins Mod 60
        Itm.subitems(6) = CStr(intHrs) & ":" & CStr(intMins)
    Else
        Itm.subitems(6) = rs![LogTotal]
    End If
End If

[salsa31]

still dosnt show the total mmm...

LsVw.ListItems.clear
 Set RS = CN.Execute("SELECT * FROM Logs ORDER BY LogId")
  
    While Not RS.EOF
        Set Itm = FrmSecurityreport.LsVw.ListItems.Add(, , RS!LogId, , "report")
        Itm.Tag = RS!LogId
        Itm.bold = True
        Itm.SubItems(1) = RS!LogDate
        Itm.SubItems(2) = RS!LogDay
        Itm.SubItems(3) = RS!LogName
        Itm.SubItems(4) = RS!LogIn
       
 If Not IsNull(RS![logout]) Then
'
' If there's a Logout time then
'   check if the LogTotal has been calculated
'
    Itm.SubItems(5) = RS![logout]
    If IsNull(RS![LogTotal]) Then
        '
        ' Calculate LogTotal
        '
        daDate1 = CDate(Itm.SubItems(4))
        daDate2 = CDate(Itm.SubItems(5))
        intMins = DateDiff("n", daDate1, daDate2)
        intHrs = intMins \ 60
        intMins = intMins Mod 60
        Itm.SubItems(6) = CStr(intHrs) & ":" & CStr(intMins)
    Else
        Itm.SubItems(6) = RS![LogTotal]
    End If

[Doogle]

My guess is that rs![logtotal] is not Null, perhaps there are spaces in it ?. Set a breakpoint on the 'If IsNull(RS![LogTotal]) Then' statement and hover the mouse over LogTotal and see what the value actually is.

[salsa31]

tnk you doogle its working
you are the best