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

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

Code:

---

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

Have a look for DATEDIFF.

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

Code:

---

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)

---

On what line does the error occurs?

Code:

---

daDate1 = CDate(Itm.SubItems(4))

---

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.

i did populate

Code:

---

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

---

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

what do you meen?
give me en example

Code:

---

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

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.

Code:

---

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

---

still dosnt show the total mmm...

Code:

---

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

---

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.

tnk you doogle its working:)
you are the best