datediff() using fields from different records

[tdb]

I am trying to calculate the session time for logon and logoff datetime fields, but the fields are in different records and different columns. Is this possible with a subquery? Here's a sample of the non-sequential fields and no sequencing field:

User ID LogonhostDate LogoffhostDate
test1 228 9/4/2006 8:52:38 PM
test1 229 9/4/2006 9:02:14 PM
test1 230 9/4/2006 9:06:59 PM
test1 231 9/4/2006 11:56:01 PM
test1 232 9/5/2006 12:01:56 AM
test1 233 9/5/2006 12:46:06 AM

I am not sure how to, or even if it;s necessary, to get the datetime fields in the same record. Any help is much appreciated.
tdb

[tdb]

Is there a way to automatically update the table when the DoCmd.RunSQL part of the function runs?

[NeoPa]

Is there a way to automatically update the table when the DoCmd.RunSQL part of the function runs?

I'm not sure what you're asking, as DoCmd.RunSQL does update the table.

[tdb]

I'm not sure what you're asking, as DoCmd.RunSQL does update the table.

When I run the build sessions function, for each record the user has to click OK on a popup window to append the table. This is for over 1000 records.

Is there a way to skip this step and just let the function run?

[MMcCarthy]

When I run the build sessions function, for each record the user has to click OK on a popup window to append the table. This is for over 1000 records.

Is there a way to skip this step and just let the function run?

You just need to add a line at the beginning of the code to turn the warnings off and a line at the end to turn them back on as follows:

 
Private Function buildSessions()
 
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strRS As String
Dim strSQL As String
Dim slogoff As Date
Set db = CurrentDb
 
  DoCmd.SetWarnings False 
 
  strRS = "SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo ORDER BY [Computerinfo].[LogonhostDate];"
  Set rs1 = db.OpenRecordset(strRS)
 
  strSQL = "SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo ORDER BY [Computerinfo].[LogoffhostDate];"
  Set rs2 = db.OpenRecordset(strSQL)
 
  rs1.MoveFirst
  Do Until rs1.EOF
If Not IsNull(rs1!LogonhostDate) Then
  rs2.MoveFirst
  Do Until rs2.EOF
If rs2!User = rs1!User And rs2!LogoffhostDate > rs1!LogonhostDate Then
  slogoff = rs2!LogoffhostDate
  rs2.MoveLast
End If
If Not rs2.EOF Then
  rs2.MoveNext
End If
  Loop
  DoCmd.RunSQL "INSERT INTO tblSessions (user, logonTime, logoffTime) " & _
  "VALUES ('" & rs1!User & "',#" & rs1!LogonhostDate & "#,#" & slogoff & "#);"
End If
rs1.MoveNext
  Loop
 
  rs1.Close
  rs2.Close
  Set rs1 = Nothing
  Set rs2 = Nothing
  Set db = Nothing
 
  DoCmd.SetWarnings True
 
End Function
 

[tdb]

Works great! This forum is a valuable resource. Thank you all for your help!

[tdb]

Something I just noticed is that the table appends to the end every time I run the function so that there are duplicate sets of sessions - is there a way to first remove all the records in the table, then build it again when the function is run?

[NeoPa]

Use it all the time :

DELETE *
FROM [YourTable]

Often with a WHERE clause too.

[MMcCarthy]

Use it all the time :

DELETE *
FROM [YourTable]

Often with a WHERE clause too.

As this is an action query it can be run with DoCmd.RunSQL

[tdb]

As this is an action query it can be run with DoCmd.RunSQL

I have tried putting this into the function in various places, tried changing the * to the [table].[field] for all fields, and tried putting it in another DoCmd.RunSQL statement. Nothing seems to work. Is there a specific place to put this statement in a function?

[MMcCarthy]

I have tried putting this into the function in various places, tried changing the * to the [table].[field] for all fields, and tried putting it in another DoCmd.RunSQL statement. Nothing seems to work. Is there a specific place to put this statement in a function?

Ok, just under DoCmd.SetWarnings False

DoCmd.RunSQL "DELETE * FROM tblSessions;"