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]

the columns did not post correctly - the LogonhostDate field is separate from the LogoffhostDate field so that each record has one or the other, but never both so that for example: ID 300 is logon date and 301 is logoff date.

[tdb]

clarification - different fields from different records

[NeoPa]

Very important question -
Can the records appear in mixed order or will the logoff for 228 ALWAYS be in 229?
Also, can multiple UserIDs be mixed up?
So, can User1 Log on,; followed by User2; then either User1 OR User2 Log off first?

[tdb]

In the original table, the order is mixed, but I sorted on user and ID in a query, then run as a parameter query pulling only one user. Sometimes either logon or logoff does not get entered into database - maybe connection was lost before updating, etc.
(If there is a way to attach a screenshot I can do that, but I don't see a button here.)

[NeoPa]

I've given this some consideration, and it seems to me that there needs to be a session ID in both the Logon & the Logoff entries. Otherwise there is no defined way to tie the two together.
Going by your posted data, there can be multiple sessions for one user in the same table.
I would say a session ID (different from the record ID) is a must for this to work.

Of course, with that it actually becomes quite easy to do.
You can GROUP BY just the session ID if it's completely unique (why not) or add the Useer if it is only unique per user.

[tdb]

I tried to find a sessionID but could not find one in Windows WMI that is unique each time user logs on; the one I found only changes if the server reboots.

If I could get the data to always alternate logon and logoff entries so that it's only the 2 columns to calculate from (without the sessionID) would it be possible to do a loop? So that using the datediff() function the startdate would come from logon column/first field, the end date would come from logoff column/second field and then alternate back and forth between columns for successive calculations?

[NeoPa]

You could do it by processing through a dataset (probably a restricted subset of the table) using VBA code and sequentially trying to match up the entries.
There would be problems though, as the data doesn't necessarily conform to how you want it. The code would have to handle all possible set of the data.
In all, not too straightforward.

[tdb]

How about this layout:
Logtype Datetime
logon 10/19/2006 3:00:00
logoff 10/192006 4:00:00
logon 10/20/2006 12:00:00
logoff 10/20/2006 1:00:00

Is there a way to pull these for the datediff() function?
It seems like it would be Datediff("n", (SELECT tbl.Datetime FROM tbl WHERE tbl.Logtype = logon), (SELECT tbl.Datetime FROM tbl WHERE tbl.Logtype=logoff)).

THanks for your help thinking this through.

[MMcCarthy]

In the following function the first recordset is based on a query based on the table you described which I'm calling tablename. The second recordset is exactly the same but with a different sort order.

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

The insert statements are to a new table called tblSessions with the fields SessionID (set to autonumber), user, logonTime, logoffTime.

Make sure the DAO library is available. In the VB editor window go to Tools - References and make sure that there is a Microsoft DAO library ticked.

Create the following function in a module:

 
Function buildSessions()
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim slogoff As Date
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("SELECT * FROM tablename ORDER BY LogonhostDate")
    Set rs2 = db.OpenRecordset("SELECT * FROM tablename ORDER BY LogoffhostDate")
    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
                rs2.MoveNext
            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
End Function
 
 

If you're not sure how to call this function let me know

[NeoPa]

Sometimes either logon or logoff does not get entered into database - maybe connection was lost before updating, etc.

I apologise if I sound churlish as this is very good code and would work perfectly if all session starts and ends were guaranteed to be logged.
If any are missing, though, this may incorrectly connect session start and end times.

Consider :
User1 ID=239 Logon@09:00
User1 logged off at 09:15 but this was not logged by the system
User1 ID=240 Logon@10:00
User1 ID=241 Logoff@10:30

This would log a session starting at 09:00 and finishing at 10:30 AS WELL AS a session starting at 10:00 and finishing at 10:30.

Using the GIGO rule, it is reasonable to expect some garbage in the results if the data's bad - but you should at least know what to expect.

[MMcCarthy]

I apologise if I sound churlish as this is very good code and would work perfectly if all session starts and ends were guaranteed to be logged.

If any are missing, though, this may incorrectly connect session start and end times.

Are you daring to argue with me <g>

[NeoPa]

NO
:Retreats hastily:

Seriously - I was quite impressed with the code - It's just the data that's a bitch.

[tdb]

Yes, I agree, this looks like it would work and put both the logon and logoff date on the same record so that I could run the datediff( ) funcion. I do need advise on how to call the function. (sorry it took so long to get back to this - we have been moving across the country and I think I got lost in a box somewhere...)
Thanks for your help! :)

In the following function the first recordset is based on a query based on the table you described which I'm calling tablename. The second recordset is exactly the same but with a different sort order.

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

The insert statements are to a new table called tblSessions with the fields SessionID (set to autonumber), user, logonTime, logoffTime.

Make sure the DAO library is available. In the VB editor window go to Tools - References and make sure that there is a Microsoft DAO library ticked.

Create the following function in a module:

 
Function buildSessions()
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim slogoff As Date
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("SELECT * FROM tablename ORDER BY LogonhostDate")
    Set rs2 = db.OpenRecordset("SELECT * FROM tablename ORDER BY LogoffhostDate")
    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
                rs2.MoveNext
            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
End Function
 
 

If you're not sure how to call this function let me know

[MMcCarthy]

Yes, I agree, this looks like it would work and put both the logon and logoff date on the same record so that I could run the datediff( ) funcion. I do need advise on how to call the function. (sorry it took so long to get back to this - we have been moving across the country and I think I got lost in a box somewhere...)
Thanks for your help! :)

The easiest way to call the function (Put it in a module by the way) is:

Put a command button on a form. Call it cmdBldSessions and put the following line in code:

 
Private Sub cmdBldSessions_Click()
    buildSessions
End Sub
 
 

[tdb]

Is there a way to pull the data in the "FROM" clause from the subform (instead of a table)? Here's what I have tried to do with the code you provided, but when I click on the button to run the function, getting a "run-time error" 3131 that the FROM statement is incorrect:
.........
Dim strSQL As String
Dim slogoff As Date
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT * FROM Forms!sessionform.subformdate.Form! ORDER BY LogonhostDate")
Set rs2 = db.OpenRecordset("SELECT * FROM Forms!sessionform.subformdate.Form! ORDER BY LogoffhostDate")
rs1.MoveFirst
Do Until rs1.EOF
If Not IsNul

[MMcCarthy]

Is there a way to pull the data in the "FROM" clause from the subform (instead of a table)? Here's what I have tried to do with the code you provided, but when I click on the button to run the function, getting a "run-time error" 3131 that the FROM statement is incorrect:
.........
Dim strSQL As String
Dim slogoff As Date
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT * FROM Forms!sessionform.subformdate.Form! ORDER BY LogonhostDate")
Set rs2 = db.OpenRecordset("SELECT * FROM Forms!sessionform.subformdate.Form! ORDER BY LogoffhostDate")
rs1.MoveFirst
Do Until rs1.EOF
If Not IsNul

Ok that won't work. However, it can be done under certain circumstances. Is the record source of subformdate a table?

[tdb]

The record source is a query - This is the way I am trying to get the date to run the function/module and get the result:

I made a form and put a combo box on it - the box is linked to a query that lets me select a user, then this is linked to a subform to populate the subform with the ID, and date fields. The subform is based on a query also, so that I could use an ordered list instead of the original table.

I hope this makes sense, maybe the info needed to make the function work could be achieved another way, but I need to be able to select a user and get the results in the right order so the function and vb code will work.

Thanks for your time...

Ok that won't work. However, it can be done under certain circumstances. Is the record source of subformdate a table?

[MMcCarthy]

OK, try this:

Dim strSQL As String
Dim slogoff As Date
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT * FROM " & Forms![sessionform]![subformdate].Form.RecordSource & " ORDER BY LogonhostDate")
Set rs2 = db.OpenRecordset("SELECT * FROM " & Forms![sessionform]![subformdate].Form.RecordSource & " ORDER BY LogoffhostDate")
rs1.MoveFirst
Do Until rs1.EOF
If Not IsNul

[tdb]

Thank you so much for your quick reply......
I am getting runtime error 2455 - "you entered an expression that has an invalid reference to the property RecordSource" - all of these fields are in the table and query that are part of this equation.

[tdb]

The subform does not show the [User] field since that is what is used to link the combo box and the subform. Could this be causing a problem? If so, is there a way to show the field that is used to link the subform?

[MMcCarthy]

OK, try this:


Dim strRS As String
Dim strSQL As String

strRS = Forms![sessionform]![subformdate].Form.RecordSource
strSQL = Left(strRS, Len(strRS) - 2) & " ORDER BY LogonhostDate"
Set rs1 = db.OpenRecordset(strSQL)

strSQL = Left(strRS, Len(strRS) - 2) & " ORDER BY LogoffhostDate"
Set rs2 = db.OpenRecordset(strSQL)

[tdb]

Same error, your code looks like it should work...I am wondering if there is anyway it is related to the subform not having the User field to work on - is there a way I can send you and image of the form?

[MMcCarthy]

Same error, your code looks like it should work...I am wondering if there is anyway it is related to the subform not having the User field to work on - is there a way I can send you and image of the form?

Sorry I just realised what you've been saying.

Yes the subform would need to have the field that is being used by the combobox.

[tdb]

Thank you so much for all your help - Is there a way to get the linked field to show up?

[tdb]

I added another field to the record source of the combo box and the record source of the subform - they are duplicate USER fields and automatically got the name Expr1000. I then reference these in the module in place of "User" but still get the Run-time error 2455. At this point it seems like I have an inherent design flaw perhaps that is preventing this report from succeeding. Any additional ideas greatly appreciated.

[MMcCarthy]

I added another field to the record source of the combo box and the record source of the subform - they are duplicate USER fields and automatically got the name Expr1000. I then reference these in the module in place of "User" but still get the Run-time error 2455. At this point it seems like I have an inherent design flaw perhaps that is preventing this report from succeeding. Any additional ideas greatly appreciated.

OK

The rules

A form and subform are linked in one of two ways.

Using a child/master relationship. This means that the field on which they are linked (usually the primary key on the main form) is present in the recordsource and in a control on the main form and also (usually a foreign key on the subform) is present in the recordsource and in a control on the subform.

OR

Using a filter on the subform. Turning it on and off and changing its value based on certain criteria on the main form. This is a little more complicated but can be done in VBA code.

Which way is most appropriate for you?

[tdb]

I have the combo box and the subform linked by child and master fields. So this way would probably be best at this point.

[PEB]

Hi tdb,

Before trying o vizualize your data using subform, it seems logical that we do a test of the code and all using a simple query?

If it works than we should assign this query as a record source of a subform and the problem will be finished Am i right?

So it will be better to assign the table with sessions as recordsource as it was by default described by mmcarthy, if there is problems in execution of this query tell us!

If not simply assign your result query in your subform and there isn't need of any comboboxes, users and so on... I think the link of your user Id with your subform data is a subject of a new thread! Do you agree with me?

Is there a way to pull the data in the "FROM" clause from the subform (instead of a table)? Here's what I have tried to do with the code you provided, but when I click on the button to run the function, getting a "run-time error" 3131 that the FROM statement is incorrect:
.........
Dim strSQL As String
Dim slogoff As Date
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT * FROM Forms!sessionform.subformdate.Form! ORDER BY LogonhostDate")
Set rs2 = db.OpenRecordset("SELECT * FROM Forms!sessionform.subformdate.Form! ORDER BY LogoffhostDate")
rs1.MoveFirst
Do Until rs1.EOF
If Not IsNul

[MMcCarthy]

I have the combo box and the subform linked by child and master fields. So this way would probably be best at this point.

OK can you post the full sql for the Record Source of both tables and tell me what you are using for the Master/Child relationship link.

[tdb]

I did what you suggest - test the code on a simple query without the combobox filtering for user - still getting the same runtime error 2455 - you entered an expression that has an invalid reference to the property RecordSource. When I debug, this line is highlighted -

strRS = Forms!sessionform!subformdate.Form.RecordSource

I tried putting the brackets around sessionform, subformdate, separate, simultaneously, still getting the same error. I tried using ! in various positions, same error.

Yes, the code works on a table, but my info is in a Form - not sure what you mean by simply assign your result query in your subform

Thank you for trying to help...

Hi tdb,

Before trying o vizualize your data using subform, it seems logical that we do a test of the code and all using a simple query?

If it works than we should assign this query as a record source of a subform and the problem will be finished Am i right?

So it will be better to assign the table with sessions as recordsource as it was by default described by mmcarthy, if there is problems in execution of this query tell us!

If not simply assign your result query in your subform and there isn't need of any comboboxes, users and so on... I think the link of your user Id with your subform data is a subject of a new thread! Do you agree with me?

[MMcCarthy]

subformdate may be the name of the subform but not necessarily the name of the subform object.

On the main form in design view click on the frame around the subform and open the properties. Under the other tab check what the value is in the Name property. Is it subformdate or something else?

The other point is whether the sessionform form is actually open when this code is executing.

If both of these are OK, try changing your code to

strRS = Forms!sessionform!subformdate.Form.RecordsetClone

and see if this will work.

If none of the above solve your problem let me know.

I did what you suggest - test the code on a simple query without the combobox filtering for user - still getting the same runtime error 2455 - you entered an expression that has an invalid reference to the property RecordSource. When I debug, this line is highlighted -

strRS = Forms!sessionform!subformdate.Form.RecordSource

I tried putting the brackets around sessionform, subformdate, separate, simultaneously, still getting the same error. I tried using ! in various positions, same error.

Yes, the code works on a table, but my info is in a Form - not sure what you mean by simply assign your result query in your subform

Thank you for trying to help...

[tdb]

OK can you post the full sql for the Record Source of both tables and tell me what you are using for the Master/Child relationship link.

I do not see where there is sql for a table, but this is what I have for the queries -

SQL for username query that is used in the combobox:
SELECT DISTINCT Computerinfo.User
FROM Computerinfo
WHERE (((Computerinfo.User) Is Not Null));

SQL for datebyuser query that is used in the subform:
SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo
ORDER BY [Computerinfo].[User], [Computerinfo].[ID];

I am using the field [User] for the CHILD LINK
and the field [Users] for the MASTER LINK (Users is the name of the combo box in the form, that way the name that is active in box is the filter for what populates the subform)

[tdb]

I checked the name and it is subformdate.
Yes, the form is open when executing.
I changed the code to RecordsetClone - now get "run-time error '31' - Type mismatch" and on debug the VB highlights same line. What a bugger!

subformdate may be the name of the subform but not necessarily the name of the subform object.

On the main form in design view click on the frame around the subform and open the properties. Under the other tab check what the value is in the Name property. Is it subformdate or something else?

The other point is whether the sessionform form is actually open when this code is executing.

If both of these are OK, try changing your code to

strRS = Forms!sessionform!subformdate.Form.RecordsetClone

and see if this will work.

If none of the above solve your problem let me know.

[MMcCarthy]

I do not see where there is sql for a table, but this is what I have for the queries -

SQL for username query that is used in the combobox:
SELECT DISTINCT Computerinfo.User
FROM Computerinfo
WHERE (((Computerinfo.User) Is Not Null));

SQL for datebyuser query that is used in the subform:
SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo
ORDER BY [Computerinfo].[User], [Computerinfo].[ID];

I am using the field [User] for the CHILD LINK
and the field [Users] for the MASTER LINK (Users is the name of the combo box in the form, that way the name that is active in box is the filter for what populates the subform)

OK this doesn't work. It only works if the Main form is also bound to a table.

Remove the values in the master child boxes and set the following in the filter instead.

[User]=[Forms]![sessionform]![Users]

Does this help?

[tdb]

I can set the filter for the form, it didn't work. Do not see how to set a filter for a subform in the properties toolbox.

OK this doesn't work. It only works if the Main form is also bound to a table.

Remove the values in the master child boxes and set the following in the filter instead.

[User]=[Forms]![sessionform]![Users]

Does this help?

[MMcCarthy]

I can set the filter for the form, it didn't work. Do not see how to set a filter for a subform in the properties toolbox.

You have to set the filter on the subform itself not the frame in the main form. Close the main form. Open the subform separately in design view and you can set it there. Then close the subform and open the main form again.

[MMcCarthy]

BTW I'm sorry I explained it badly in the first place. The filter only goes on the subform. Not on the main form.

[tdb]

BTW I'm sorry I explained it badly in the first place. The filter only goes on the subform. Not on the main form.

I am using Access 2000 and I do not see any option to open only the subform. I tried double clicking on it. Sorry for yhe hassle, I really appreciate you trying to help me out.

[MMcCarthy]

I am using Access 2000 and I do not see any option to open only the subform. I tried double clicking on it. Sorry for yhe hassle, I really appreciate you trying to help me out.

Check your list of forms. When you create a subform, even using the wizard it should be saved on its own as a form separately on the list.

[tdb]

Check your list of forms. When you create a subform, even using the wizard it should be saved on its own as a form separately on the list.

Ok, I figured out how to set the filter, still getting error "type mismatch" - Could it be that we are using "str" and the values are both alpha and numerical. Is there another way to define the variable?

Dim strRS As String
....
strRS = Forms![sessionform]![subform].Form.RecordsetClone
...

[MMcCarthy]

Ok, I figured out how to set the filter, still getting error "type mismatch" - Could it be that we are using "str" and the values are both alpha and numerical. Is there another way to define the variable?

Dim strRS As String
....
strRS = Forms![sessionform]![subform].Form.RecordsetClone
...

Sorry I made an assumption you had declared a recordset. I should have noticed the Set command was missing.

[code]

Dim strRS As Recordset

Set strRS = Forms![sessionform]![subform].Form.RecordsetClone

/code]

[tdb]

Sorry I made an assumption you had declared a recordset. I should have noticed the Set command was missing.

[code]

Dim strRS As Recordset

Set strRS = Forms![sessionform]![subform].Form.RecordsetClone

/code]

This seems to be working - but now gettin another error at

Set rs1 = db.OpenRecordset(strSQL)

"type mismatch" at the ".OpenRecordset"
I tried OpenDynaset, didn't work.
I changed the strSQL to be like the strRS.

[MMcCarthy]

This seems to be working - but now gettin another error at

Set rs1 = db.OpenRecordset(strSQL)

"type mismatch" at the ".OpenRecordset"
I tried OpenDynaset, didn't work.
I changed the strSQL to be like the strRS.

OK

The code is all mixed up. You shouldn't have that line at all.

Can you post the code IN FULL

Mary

[tdb]

Private Function buildSessions()

Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strRS As DAO.Recordset
Dim strSQL As DAO.Recordset
Dim slogoff As Date
Set db = CurrentDb

Set strRS = Forms![sessionform]![subform].Form.RecordsetClone
Set strSQL = Left(strRS, "Len(strRS)" - 2) & " ORDER BY LogonhostDate"
Set rs1 = db.OpenRecordset(strSQL)

Set strSQL = Left(strRS, "Len(strRS)" - 2) & " ORDER BY 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
rs2.MoveNext
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
End Function

[MMcCarthy]

I just need a couple more things.

What is the record source of the form subform. Is it a table, saved query or select statement. If it's a saved query or select statement I'll need the full sql.

The problem is you can't use recordsetclone the way you're using it. If you give me the above info I'll be able to work out the correct approach.

Mary

[tdb]

I just need a couple more things.

What is the record source of the form subform. Is it a table, saved query or select statement. If it's a saved query or select statement I'll need the full sql.

The problem is you can't use recordsetclone the way you're using it. If you give me the above info I'll be able to work out the correct approach.

Mary

It is a saved query - here it is.....thankyou, hope this helps.

SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo
ORDER BY [Computerinfo].[User], [Computerinfo].[ID];

[MMcCarthy]

SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo
ORDER BY [Computerinfo].[User], [Computerinfo].[ID];

This statement already has an Order By you can't impose another one in the code. Therefore you cannot use RecordsetClone.

I 've changed the code to reflect this and to correct errors in the recordset assignment.

 
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
 
  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
 
End Function
 
 

[tdb]

Mary - you did it! Kudos! Thank you sooo much for all your help! This is great. I will study what you have done here to learn more about sql.

SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo
ORDER BY [Computerinfo].[User], [Computerinfo].[ID];

This statement already has an Order By you can't impose another one in the code. Therefore you cannot use RecordsetClone.

I 've changed the code to reflect this and to correct errors in the recordset assignment.

 
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
 
  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
 
End Function
 
 

[MMcCarthy]

Mary - you did it! Kudos! Thank you sooo much for all your help! This is great. I will study what you have done here to learn more about sql.

Great, I'm glad it's working.

If you've any questions on what I've done or why just let me know.

Mary